protected override bool LockDocument(string ids)
{
ids = string.Format("'{0}'", 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_occupy_finance=@is_occupy_finance where sale_billing_id in ({0})", ids);
saleBilling.Param.Add("is_occupy_finance", ((int)DataSources.EnumImportStaus.OCCUPY).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_occupy_finance=@is_occupy_finance where settlement_id in ({0})", ids);
maintainSql.Param = new Dictionary <string, string>();
maintainSql.Param.Add("is_occupy_finance", ((int)DataSources.EnumImportStaus.OCCUPY).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_occupy_finance=@is_occupy_finance where st_id in ({0})", ids);
threeSql.Param = new Dictionary <string, string>();
threeSql.Param.Add("is_occupy_finance", ((int)DataSources.EnumImportStaus.OCCUPY).ToString());
//threeSql.Param.Add("id", ids);
listSql.Add(threeSql);
return(DBHelper.BatchExeSQLStringMultiByTrans("锁定应收单据", listSql));
}
/// <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 sys_b_set_repair_package_set set status=@status where repair_package_set_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));
}
//确认，保存到数据库并关闭当前窗体
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();
}
}
private IEnumerable <SysSQLString> BuildVehicleRelation(String custId) //生成车辆与客户关系SQL
{
var sysSqlStrList = new List <SysSQLString>();
if (String.IsNullOrEmpty(custId))
{
return(sysSqlStrList);
}
foreach (DataGridViewRow row in dgvVehicle.Rows)
{
if (String.IsNullOrEmpty(CommonCtrl.IsNullToString(row.Cells["tb_vehicle_v_id"].Value)))
{
continue;
}
var sysSqlStrUpdate = new SysSQLString
{
sqlString = "update tb_vehicle set cust_id = @cust_id, update_time = @update_time, update_by = @update_by where v_id = @v_id"
};
sysSqlStrUpdate.Param = new Dictionary <string, string>();
sysSqlStrUpdate.Param.Add("cust_id", custId);
sysSqlStrUpdate.Param.Add("update_by", GlobalStaticObj.UserID);
sysSqlStrUpdate.Param.Add("update_time", Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime).ToString());
sysSqlStrUpdate.Param.Add("v_id", row.Cells["tb_vehicle_v_id"].Value.ToString());
sysSqlStrList.Add(sysSqlStrUpdate);
}
return(sysSqlStrList);
}
/// <summary>
/// 添加收付款明细sql
/// </summary>
/// <param name="list"></param>
static void AddDetailSqlString(List <SysSQLString> list, tb_payment_detail detail)
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary <string, string>();
sql.Param.Add("order_id", detail.order_id);
sql.Param.Add("money", detail.money.ToString()); //金额
sql.Param.Add("balance_way", detail.balance_way); //结算方式
//sql.Param.Add("payment_account", detail.payment_account);//付款账户
//sql.Param.Add("bank_of_deposit", detail.bank_of_deposit);//开户银行
//sql.Param.Add("bank_account", detail.bank_account);//银行账户
sql.Param.Add("check_number", detail.check_number);//票号
//sql.Param.Add("remark", detail.remark);//备注
sql.Param.Add("create_by", GlobalStaticObj.UserID);
sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());
// sql.sqlString = @"INSERT INTO [tb_payment_detail](payment_detail_id,order_id,money
//,balance_way,payment_account,bank_of_deposit,bank_account,check_number,remark,create_by,create_time)
// VALUES
//(@payment_detail_id,@order_id,@money,@balance_way,@payment_account,@bank_of_deposit,@bank_account,@check_number,@remark
//,@create_by,@create_time)";
sql.Param.Add("payment_detail_id", Guid.NewGuid().ToString());
sql.sqlString = @"INSERT INTO [tb_payment_detail](payment_detail_id,order_id,money
,balance_way,payment_account,bank_of_deposit,bank_account,check_number,create_by,create_time)
select @payment_detail_id,@order_id,@money,a.balance_way_id,a.default_account,b.bank_name,b.bank_account,
@check_number,@create_by,@create_time from tb_balance_way a
left join v_cashier_account b on a.default_account=b.cashier_account
where a.balance_way_id=@balance_way";
list.Add(sql);
}
/// <summary>
/// 编辑情况下组装sql的方法
/// </summary>
/// <param name="listSql"></param>
/// <param name="purchase_billing_id"></param>
/// <param name="model"></param>
private void EditReceiptBillSql(List <SysSQLString> listSql, tb_parts_stock_shipping stockShippingEntity, string StockShippingtId, string HandleType)
{
try
{
const string NoDelFlag = "1"; //默认删除标记，1表示未删除，0表示删除
string Save = DataSources.GetDescription(DataSources.EnumOperateType.save, true); //保存操作
string Submit = DataSources.GetDescription(DataSources.EnumOperateType.submit, true); //提交操作
SysSQLString sysStrSql = new SysSQLString();
sysStrSql.cmdType = CommandType.Text; //sql字符串语句执行函数
Dictionary <string, string> dicParam = new Dictionary <string, string>(); //参数
CommonFuncCall.FillEntityByControls(this, ShippingBillEntity);
stockShippingEntity.handle = GlobalStaticObj.UserID;
stockShippingEntity.operators = GlobalStaticObj.UserID;
stockShippingEntity.enable_flag = NoDelFlag;
if (HandleType == Save)
{
stockShippingEntity.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString();
stockShippingEntity.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true);
}
else if (HandleType == Submit)
{
stockShippingEntity.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString();
stockShippingEntity.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true);
}
if (ShippingBillEntity != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update tb_parts_stock_shipping Set ");
bool isFirstValue = true;
foreach (PropertyInfo info in stockShippingEntity.GetType().GetProperties())
{
string name = info.Name;
object value = info.GetValue(stockShippingEntity, 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 stock_shipping_id='" + StockShippingtId + "';");
sysStrSql.sqlString = sb.ToString();
sysStrSql.Param = dicParam;
listSql.Add(sysStrSql);//完成SQL语句的拼装
}
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary> 审核事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void UCSaleOrderManager_VerifyEvent(object sender, EventArgs e)
{
List <string> listField = GetVerifyRecord();
if (listField.Count == 0)
{
MessageBoxEx.Show("请选择要审核的数据!");
return;
}
UCVerify UcVerify = new UCVerify();
if (UcVerify.ShowDialog() == DialogResult.OK)
{
string Content = UcVerify.Content;
SYSModel.DataSources.EnumAuditStatus UcVerifyStatus = UcVerify.auditStatus;
List <SysSQLString> list_sql = new List <SysSQLString>();
for (int i = 0; i < listField.Count; i++)
{
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary <string, string> dic = new Dictionary <string, string>();
if (UcVerifyStatus == DataSources.EnumAuditStatus.AUDIT)
{
//获取销售订单状态(已审核)
dic.Add("order_status", Convert.ToInt32(DataSources.EnumAuditStatus.AUDIT).ToString());
dic.Add("order_status_name", DataSources.GetDescription(DataSources.EnumAuditStatus.AUDIT, true));
}
else if (UcVerifyStatus == DataSources.EnumAuditStatus.NOTAUDIT)
{
//获取销售订单状态(审核不通过)
dic.Add("order_status", Convert.ToInt32(DataSources.EnumAuditStatus.NOTAUDIT).ToString());
dic.Add("order_status_name", DataSources.GetDescription(DataSources.EnumAuditStatus.NOTAUDIT, true));
}
dic.Add("update_by", GlobalStaticObj.UserID); //修改人Id
dic.Add("update_name", GlobalStaticObj.UserName); //修改人姓名
dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString()); //修改时间
dic.Add("sale_order_id", listField[i]);
sysStringSql.sqlString = @"update tb_parts_sale_order set
order_status=@order_status,order_status_name=@order_status_name,
update_by=@update_by,update_name=@update_name,update_time=@update_time
where sale_order_id=@sale_order_id";
sysStringSql.Param = dic;
list_sql.Add(sysStringSql);
}
if (DBHelper.BatchExeSQLStringMultiByTrans("销售订单审核操作", list_sql))
{
if (UcVerifyStatus == DataSources.EnumAuditStatus.AUDIT)
{
AddBillPayReceive(listField);
}
BindgvSaleOrderList();
MessageBoxEx.Show("操作成功！");
}
else
{
MessageBoxEx.Show("操作失败！");
}
}
}
//保存
void UC_SaveEvent(object sender, EventArgs e)
{
string msg = string.Empty;
if (!this.ValidatorDgv(ref msg))
{
MessageBoxEx.Show(msg, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
this.dgvRecord.EndEdit();
List <SysSQLString> sqlList = new List <SysSQLString>();
string max = string.Empty;
string min = string.Empty;
string id = string.Empty;
string wh_id = string.Empty;
string parts_id = string.Empty;
foreach (int index in this.rowList)
{
id = this.dgvRecord[this.columnId.Name, index].Value.ToString();
max = this.dgvRecord[this.columnMax.Name, index].Value.ToString();
min = this.dgvRecord[this.columnMin.Name, index].Value.ToString();
wh_id = this.dgvRecord[this.column_wh_id.Name, index].Value.ToString();
parts_id = this.dgvRecord[this.column_parts_id.Name, index].Value.ToString();
SysSQLString sysSql = new SysSQLString();
sysSql.cmdType = CommandType.Text;
sysSql.Param = new Dictionary <string, string>();
if (id.Length > 0)
{
//修改
sysSql.sqlString += string.Format("update tr_hourse_parts set max_num={0},min_num={1} where id='{2}'", max, min, id);
}
else
{
//新增
sysSql.sqlString += string.Format("insert into tr_hourse_parts(id,wh_id,parts_id,max_num,min_num,create_by,create_time) values ('{0}','{1}','{2}',{3},{4},'{5}',{6})"
, Guid.NewGuid().ToString(), wh_id, parts_id, max, min, HXCPcClient.GlobalStaticObj.UserID, Common.LocalDateTimeToUtcLong(HXCPcClient.GlobalStaticObj.CurrentDateTime));
}
sqlList.Add(sysSql);
}
if (DBHelper.BatchExeSQLStringMultiByTrans("编辑仓库配件关系", sqlList))
{
MessageBoxEx.Show("保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
btnQuery.PerformClick();
foreach (int index in this.rowList)
{
this.dgvRecord.Rows[index].ReadOnly = true;
SetDgvEditCellBgColor(this.dgvRecord.Rows[index], new string[] { this.columnMax.Name, this.columnMin.Name }, false);
}
}
else
{
MessageBoxEx.Show("保存失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
/// <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("order_id", orderId); //单据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_BackUp", "order_status,order_status_name", "order_id='" + orderId + "'", "", "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();
order_status_name = !string.IsNullOrEmpty(order_status_name) ? 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 set order_status=@order_status,order_status_name=@order_status_name,update_by=@update_by,update_name=@update_name,update_time=@update_time where order_id=@order_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.BindgvPurchaseOrderList();
deleteMenuByTag(this.Tag.ToString(), uc.Name);
}
else
{
MessageBoxEx.Show("" + strmsg + "失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
//报表加载
private void UCReport_Load(object sender, EventArgs e)
{
#region 设置
//设置页面按钮可见性
var btnCols = new ObservableCollection <ButtonEx_sms>
{
btnExport, btnView, btnPrint, btnSet
};
UIAssistants.SetUCBaseFuncationVisible(this, btnCols);
#endregion
UIAssistants.SetButtonStyle4QueryAndClear(btnSearch, btnClear); //设置查询按钮和清除按钮样式
if (string.IsNullOrEmpty(styleObject))
{
return;
}
foreach (Control ctr in pnlReport.Controls)
{
if (ctr is DataGridViewReport)
{
DataGridViewReport dgv = (DataGridViewReport)ctr;
#region 判断是否有当前用户、当前报表的设置，如果没有则创建
if (!DBHelper.IsExist("", "tb_report_set", string.Format("set_object='{0}' and set_user='******'", styleObject, GlobalStaticObj.UserID)))
{
List <SysSQLString> listSql = new List <SysSQLString>();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible)
{
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)
values (@set_id,@set_num,@set_object,@set_user,@set_name,@set_data_name,@set_width,@is_show,@is_print)";
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", styleObject);
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");
sql.Param.Add("is_print", "1");
listSql.Add(sql);
}
}
if (listSql.Count > 0)
{
DBHelper.BatchExeSQLStringMultiByTrans("", listSql);
}
}
#endregion
dgv.CellFormatting += new DataGridViewCellFormattingEventHandler(dgv_CellFormatting);
break;
}
}
SetReportSet();
}
/// <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);
}
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="sender"></param>
/// <param name="e"></param>
void UCSalePlanManager_SubmitEvent(object sender, EventArgs e)
{
try
{
if (MessageBoxEx.Show("确认要提交选中的数据吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
{
return;
}
List <SysSQLString> listSql = new List <SysSQLString>();
string strReceId = string.Empty;//单据Id值
foreach (DataGridViewRow dr in gvSalePlanList.Rows)
{
object isCheck = dr.Cells["colCheck"].EditedFormattedValue;
if (isCheck != null && (bool)isCheck)
{
strReceId += dr.Cells["sale_plan_id"].Value.ToString() + ",";
SysSQLString obj = new SysSQLString();
obj.cmdType = CommandType.Text;
Dictionary <string, string> dicParam = new Dictionary <string, string>();
string order_num = string.Empty;
if (dr.Cells["colorder_status"].Value.ToString() == Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString())//草稿状态
{
order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.SalePlan);
}
else if (dr.Cells["colorder_status"].Value.ToString() == Convert.ToInt32(DataSources.EnumAuditStatus.NOTAUDIT).ToString())//审核未通过
{
order_num = dr.Cells["colorder_num"].Value.ToString();
}
dicParam.Add("order_num", order_num); //单据编号
dicParam.Add("sale_plan_id", dr.Cells["sale_plan_id"].Value.ToString()); //单据ID
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_sale_plan 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 sale_plan_id=@sale_plan_id";
obj.Param = dicParam;
listSql.Add(obj);
}
}
if (string.IsNullOrEmpty(strReceId.Replace(",", "")))
{
MessageBoxEx.Show("请选择需要提交的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (DBHelper.BatchExeSQLStringMultiByTrans("更新单据状态为提交", listSql))
{
MessageBoxEx.Show("提交成功！");
BindgvSalePlanList();
}
}
catch (Exception ex)
{ }
}
/// <summary>
/// 往来核销重新计算单据的已结算,待结算金额
/// </summary>
/// <param name="enumAccount">单据类型</param>
/// <param name="id">单据ID</param>
/// <param name="list"></param>
public static void VerificationDocumentMoney(DataSources.EnumAccountVerification enumAccount, string id, List <SysSQLString> list)
{
//不为预收转预收，预付转预付的情况，重新计算单据的已结算,待结算金额
if (enumAccount != DataSources.EnumAccountVerification.YuShouToYuShou && enumAccount != DataSources.EnumAccountVerification.YuFuToYuFu)
{
SysSQLString sqlSettlement = new SysSQLString();
sqlSettlement.cmdType = CommandType.Text;
sqlSettlement.Param = new Dictionary <string, string>();
sqlSettlement.Param.Add("order_id", id);
string orderIndex = "1";
//应收Sql
string yingShouSql = @"update a set settled_money=b.money,wait_settled_money=a.money-ISNULL(b.money,0)
from tb_verificationn_documents a left join v_YingShou b on a.order_id=b.documents_id
where a.account_verification_id=@order_id and order_index=@order_index";
//应付Sql
string yingFuSql = @"update a set settled_money=b.money,wait_settled_money=a.money-ISNULL(b.money,0)
from tb_verificationn_documents a left join v_YingFu b on a.order_id=b.documents_id
where a.account_verification_id=@order_id and order_index=@order_index";
//预收冲应收，应收转应收
if (enumAccount == DataSources.EnumAccountVerification.YuShouToYingShou || enumAccount == DataSources.EnumAccountVerification.YingShouToYingShou)
{
sqlSettlement.sqlString = yingShouSql;
}
//预付冲应付，应付转应付
else if (enumAccount == DataSources.EnumAccountVerification.YuFuToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingFu)
{
sqlSettlement.sqlString = yingFuSql;
}
//应收冲应付，应付冲应收
if (enumAccount == DataSources.EnumAccountVerification.YingShouToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou)
{
//应收已结算金额
sqlSettlement.sqlString = yingShouSql;
//应付已结算金额
SysSQLString sqlSettlement1 = new SysSQLString();
sqlSettlement1.cmdType = CommandType.Text;
sqlSettlement1.Param = new Dictionary <string, string>();
sqlSettlement1.Param.Add("order_id", id);
sqlSettlement1.sqlString = yingFuSql;
if (enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou)
{
orderIndex = "2";
sqlSettlement1.Param.Add("order_index", "1");
}
else
{
sqlSettlement1.Param.Add("order_index", "2");
}
list.Add(sqlSettlement1);
}
sqlSettlement.Param.Add("order_index", orderIndex);
list.Add(sqlSettlement);
}
}
/// <summary>
/// 添加情况下组装sql的方法
/// </summary>
/// <param name="listSql"></param>
/// <param name="purchase_billing_id"></param>
private void AddShippingBillSql(List <SysSQLString> listSql, tb_parts_stock_shipping stockShippingEntity, string StockShippingId, string HandleType)
{
try
{
const string NoDelFlag = "1"; //默认删除标记1表示未删除，0表示删除
string Save = DataSources.GetDescription(DataSources.EnumOperateType.save, true); //保存操作
string Submit = DataSources.GetDescription(DataSources.EnumOperateType.submit, true); //提交操作
//SQL语句拼装操作
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary <string, string> dicParam = new Dictionary <string, string>();//保存SQL语句参数值
CommonFuncCall.FillEntityByControls(this, stockShippingEntity);
stockShippingEntity.stock_shipping_id = StockShippingId;
stockShippingEntity.update_by = GlobalStaticObj.UserID;
stockShippingEntity.operators = GlobalStaticObj.UserID;
stockShippingEntity.enable_flag = NoDelFlag;
if (HandleType == Save)
{
stockShippingEntity.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString();
stockShippingEntity.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true);
}
else if (HandleType == Submit)
{
stockShippingEntity.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString();
stockShippingEntity.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true);
}
if (stockShippingEntity != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into tb_parts_stock_shipping( ");
StringBuilder sb_PrValue = new StringBuilder();
StringBuilder sb_PrName = new StringBuilder();
foreach (PropertyInfo info in stockShippingEntity.GetType().GetProperties())
{
string name = info.Name;
object value = info.GetValue(stockShippingEntity, null);
sb_PrName.Append("," + name); //数据表字段名
sb_PrValue.Append(",@" + name); //数据表字段值
dicParam.Add(name, value == null ? "" : value.ToString());
}
sb.Append(sb_PrName.ToString().Substring(1, sb_PrName.ToString().Length - 1) + ") Values ("); //追加字段名
sb.Append(sb_PrValue.ToString().Substring(1, sb_PrValue.ToString().Length - 1) + ");"); //追加字段值
//完成SQL语句的拼装
sysStringSql.sqlString = sb.ToString();
sysStringSql.Param = dicParam;
listSql.Add(sysStringSql);
}
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <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="listSql"></param>
/// <param name="supID"></param>
private void DealContacts(List <SysSQLString> listSql, string relation_id)
{
gvUserInfoList.EndEdit();
SysSQLString sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
Dictionary <string, string> dic = new Dictionary <string, string>();
if (gvUserInfoList.Rows.Count > 0)
{
string sql1 = "delete from tr_base_contacts where relation_object_id=@relation_object_id;";
dic.Add("relation_object_id", relation_id);
sysStringSql.sqlString = sql1;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
foreach (DataGridViewRow dr in gvUserInfoList.Rows)
{
sysStringSql = new SysSQLString();
sysStringSql.cmdType = CommandType.Text;
dic = new Dictionary <string, string>();
string contId = dr.Cells["colcont_id"].Value.ToString();
dic.Add("id", Guid.NewGuid().ToString());
dic.Add("cont_id", contId);
dic.Add("relation_object", "供应商档案");
dic.Add("relation_object_id", relation_id);
string is_default = "0";
if (dr.Cells["colis_default"].Value == null)
{
is_default = "0";
}
if ((bool)dr.Cells["colis_default"].EditedFormattedValue)
{
is_default = "1";
}
else
{
is_default = "0";
}
dic.Add("is_default", is_default);
string sql2 = "Insert Into tr_base_contacts(id,cont_id,relation_object,relation_object_id,is_default) values(@id,@cont_id,@relation_object,@relation_object_id,@is_default);";
sysStringSql.sqlString = sql2;
sysStringSql.Param = dic;
listSql.Add(sysStringSql);
}
}
}
/// <summary>
/// 删除角色菜单关联
/// </summary>
/// <param name="id"></param>
/// <param name="funIdList"></param>
/// <returns></returns>
private bool DeleteFunRole(string id, ArrayList funIdList)
{
List <SysSQLString> listSql = new List <SysSQLString>();
foreach (string fun_id in funIdList)
{
SysSQLString sqlString = new SysSQLString();
sqlString.cmdType = CommandType.Text;
sqlString.sqlString = string.Format("delete from tr_role_function where role_id='{0}' and fun_id ='{1}'", id, fun_id);
sqlString.Param = new Dictionary <string, string>();
listSql.Add(sqlString);
}
return(DBHelper.BatchExeSQLStringMultiByTrans("", listSql));
}
//报表加载
private void UCReport_Load(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(styleObject))
{
return;
}
foreach (Control ctr in pnlReport.Controls)
{
if (ctr is DataGridView)
{
DataGridView dgv = (DataGridView)ctr;
#region 判断是否有当前用户、当前报表的设置，如果没有则创建
if (!DBHelper.IsExist("", "tb_report_set", string.Format("set_object='{0}' and set_user='******'", styleObject, GlobalStaticObj.UserID)))
{
List <SysSQLString> listSql = new List <SysSQLString>();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible)
{
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", styleObject);
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");
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
dgv.CellFormatting += new DataGridViewCellFormattingEventHandler(dgv_CellFormatting);
break;
}
}
SetReportSet();
}
//提交,只有草稿状态才可提交
void SubmitData()
{
dgvVerification.EndEdit();
List <SysSQLString> listSql = new List <SysSQLString>();
string submit = ((int)DataSources.EnumAuditStatus.SUBMIT).ToString(); //提交
string draft = ((int)DataSources.EnumAuditStatus.DRAFT).ToString(); //草稿
string notAudit = ((int)DataSources.EnumAuditStatus.NOTAUDIT).ToString(); //审核失败
foreach (DataGridViewRow dgvr in dgvVerification.Rows)
{
object isCheck = dgvr.Cells[colChk.Name].Value;
string status = CommonCtrl.IsNullToString(dgvr.Cells[colOrderStatus.Name].Tag);
if (isCheck != null && (bool)isCheck && (status == draft || status == notAudit))
{
SysSQLString sql = new SysSQLString();
sql.cmdType = CommandType.Text;
sql.Param = new Dictionary <string, string>();
sql.Param.Add("submit", submit);
sql.Param.Add("draft", draft);
sql.Param.Add("notAudit", notAudit);
sql.Param.Add("order_num", CommonUtility.GetNewNo(DataSources.EnumProjectType.PAYMENT));
string order_id = dgvr.Cells[colAccountVerificationID.Name].Value.ToString();
sql.Param.Add("account_verification_id", order_id);
sql.sqlString = "update tb_account_verification set order_status=@submit,order_num=@order_num where account_verification_id=@account_verification_id and (order_status=@draft or order_status=@notAudit)";
listSql.Add(sql);
SetDocumentImportStatus("is_lock", DataSources.EnumImportStaus.LOCK, listSql, order_id, dgvr.Cells[colOrderType.Name].Tag.ToString());
DataSources.EnumAccountVerification enumAccount = (DataSources.EnumAccountVerification)Convert.ToInt32(dgvr.Cells[colOrderType.Name].Tag);
Financial.DocumentSettlementByVerification(enumAccount, order_id, listSql);
}
}
if (listSql.Count == 0)
{
MessageBoxEx.Show("请选择要提交的数据！");
return;
}
if (!MessageBoxEx.ShowQuestion("是否要提交选择的数据！"))
{
return;
}
if (DBHelper.BatchExeSQLStringMultiByTrans("提交应收应付", listSql))
{
MessageBoxEx.Show("提交成功！");
BindData();
}
else
{
MessageBoxEx.ShowWarning("提交失败！");
}
}
private IEnumerable <SysSQLString> BuildVipMemberSqlString(String custId) //生成会员信息SQL
{
var sysSqlStrList = new List <SysSQLString>();
if (String.IsNullOrEmpty(custId))
{
return(sysSqlStrList);
}
var sssCust = new SysSQLString {
Param = new Dictionary <string, string>()
};
switch (wStatus)
{
case WindowStatus.Copy:
case WindowStatus.Add:
{
sssCust.sqlString =
"insert into tb_CustomerSer_member (vip_id,corp_id,vip_code,member_grade,remark,validity_time,integral,status,enable_flag,create_by,create_time,update_by,update_time) values (@vip_id,@corp_id,@vip_code,@member_grade,@remark,@validity_time,@integral,@status,@enable_flag,@create_by,@create_time,@update_by,@update_time)";
sssCust.Param.Add("vip_id", Guid.NewGuid().ToString());
sssCust.Param.Add("vip_code", CommonUtility.GetNewNo(DataSources.EnumProjectType.CustomerSer_member));
sssCust.Param.Add("corp_id", custId);
sssCust.Param.Add("member_grade", CommonCtrl.IsNullToString(cbomember_class.SelectedValue));
sssCust.Param.Add("status", "1");
sssCust.Param.Add("remark", "");
sssCust.Param.Add("validity_time", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dtpmember_period_validity.Value)).ToString());
sssCust.Param.Add("integral", "0");
sssCust.Param.Add("enable_flag", "1");
sssCust.Param.Add("create_by", GlobalStaticObj.UserID);
sssCust.Param.Add("create_time", Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime).ToString());
sssCust.Param.Add("update_by", GlobalStaticObj.UserID);
sssCust.Param.Add("update_time", Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime).ToString());
}
break;
case WindowStatus.Edit:
sssCust.sqlString =
"update tb_CustomerSer_member set member_grade = @member_grade,remark = @remark,validity_time = @validity_time,update_by = @update_by,update_time = @update_time where vip_code = @vip_code";
sssCust.Param.Add("vip_code", txtmember_number.Caption);
sssCust.Param.Add("member_grade", CommonCtrl.IsNullToString(cbomember_class.SelectedValue));
sssCust.Param.Add("remark", "");
sssCust.Param.Add("validity_time", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dtpmember_period_validity.Value)).ToString());
sssCust.Param.Add("update_by", GlobalStaticObj.UserID);
sssCust.Param.Add("update_time", Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime).ToString());
break;
}
sysSqlStrList.Add(sssCust);
return(sysSqlStrList);
}
/// <summary>
/// 添加角色用户关系
/// </summary>
/// <param name="roleid">角色id</param>
/// <param name="userIdList">用户Id</param>
private bool AddUserRole(string roleId, string userId)
{
List <SysSQLString> listSql = new List <SysSQLString>();
SysSQLString sqlString = new SysSQLString();
sqlString.cmdType = CommandType.Text;
sqlString.sqlString = string.Format("insert into tr_user_role (user_role_id,user_id,role_id) values ('{0}','{1}','{2}')",
Guid.NewGuid().ToString(), userId, roleId);
sqlString.Param = new Dictionary <string, string>();
listSql.Add(sqlString);
return(DBHelper.BatchExeSQLStringMultiByTrans("新增用户角色权限", listSql));
}
public static void UserLoginOut(string UserID, string currAccCode)
{
DateTime dt = DBHelper.GetCurrentTime(GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode);
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("系统登出", currAccCode, list);
}
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)
{
}
}
public void GetAttachmentSql(List <SysSQLString> listSql)
{
foreach (DataGridViewRow dgvr in dgvAttachment.Rows)
{
//如果什么都没有，则不保存了
if (CommonCtrl.IsNullToString(dgvr.Cells[colAttName.Name].Value).Length == 0 &&
CommonCtrl.IsNullToString(dgvr.Cells[colAttType.Name].Value).Length == 0 &&
CommonCtrl.IsNullToString(dgvr.Cells[colAttPath.Name].Tag).Length == 0 &&
CommonCtrl.IsNullToString(dgvr.Cells[colAttRemark.Name].Value).Length == 0)
{
continue;
}
SysSQLString obj = new SysSQLString();
obj.cmdType = CommandType.Text;
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("att_name", CommonCtrl.IsNullToString(dgvr.Cells["colAttName"].Value));
dic.Add("att_type", CommonCtrl.IsNullToString(dgvr.Cells["colAttType"].Value));
dic.Add("att_path", CommonCtrl.IsNullToString(dgvr.Cells["colAttPath"].Tag));
string fileName = Path.GetFileName(dgvr.Cells["colAttPath"].Value.ToString());
dic.Add("att_filename", fileName);
dic.Add("remark", CommonCtrl.IsNullToString(dgvr.Cells["colAttRemark"].Value));
obj.Param = dic;
string attID = CommonCtrl.IsNullToString(dgvr.Cells["colAttID"].Value);
if (attID.Length == 0)
{
attID = Guid.NewGuid().ToString();
dic.Add("is_main", ((int)DataSources.EnumYesNo.NO).ToString());
dic.Add("relation_object", TableName);
dic.Add("relation_object_id", TableNameKeyValue);
dic.Add("enable_flag", ((int)DataSources.EnumEnableFlag.USING).ToString());
dic.Add("create_by", GlobalStaticObj.UserID);
dic.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.UtcNow).ToString());
obj.sqlString = @"insert into [attachment_info] ([att_id],[att_name],[att_type],[att_path],[att_filename],[remark],[relation_object],[relation_object_id],[enable_flag],[create_by],[create_time],[is_main])
values (@att_id,@att_name,@att_type,@att_path,@att_filename,@remark,@relation_object,@relation_object_id,@enable_flag,@create_by,@create_time,@is_main);";
}
else
{
dic.Add("update_by", GlobalStaticObj.UserID);
dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.UtcNow).ToString());
obj.sqlString = @"update [attachment_info] set [att_name]=@att_name,[att_type]=@att_type,[att_path]=@att_path,[att_filename]=@att_filename,[remark]=@remark,
[update_by]=@update_by,[update_time]=@update_time where [att_id]=@att_id;";
}
dic.Add("att_id", attID);
listSql.Add(obj);
}
}
/// <summary> 提交时获取当前配件列表中存在的引用单号,保存到中间表中
/// 并生成执行的sql
/// </summary>
/// <returns></returns>
void GetPre_Order_Code(List <SysSQLString> listSql, string sale_order_id, 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;";
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);
DataTable dt_relation_order = DBHelper.GetTable("查询销售订单配件表的引用单号", "tb_parts_sale_order_p", " sale_order_id,relation_order ", " sale_order_id='" + sale_order_id + "'", "", "");
if (dt_relation_order != null && dt_relation_order.Rows.Count > 0)
{
foreach (DataRow dr in dt_relation_order.Rows)
{
string relation_order = dr["relation_order"] == null ? "" : dr["relation_order"].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="orderType">应收应付单据类型</param>
/// <param name="id">应收应付ID</param>
/// <param name="list">sql列表</param>
public static void DocumentAdvanceByBill(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);
if (orderType == DataSources.EnumOrderType.RECEIVABLE)
{
sqlSettlement.sqlString = "p_yushou_jiesuan";
}
else
{
sqlSettlement.sqlString = "p_yufu_jiesuan";
}
list.Add(sqlSettlement);
}
/// <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
}
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);
}
