/// <summary> /// 查询基础功能模块的子节点信息 /// </summary> public void QueryBaseStnModule_OnlyLeaf(DataTable queryDataTable, string smNoStr) { string sqlStr = string.Format("select SMNo + '-' + Cast(AutoId as varchar) as ReID, SMNo as ReParent, DeliveryText as FunctionDesc, CAST(DeliveryQty as varchar) + ' * ' + CAST(Convert(decimal(10,2), Unit) as varchar) + ' = ' + CAST(Amount as varchar) as Detail from SA_DeliveryDetail where SMNo = '{0}'", smNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 检测数据库中预算外出库单状态是否可以操作 /// </summary> public bool CheckWarehouseState(DataTable swwHeadTable, DataTable swwListTable, string specialWarehouseReceiptListStr, bool checkNoApprover, bool checkApprover, bool checkSettle, bool checkApproverBetween) { string sqlStr = string.Format("select SpecialWarehouseReceipt, WarehouseState from INV_SpecialWarehouseReceiptHead where SpecialWarehouseReceipt in ({0})", specialWarehouseReceiptListStr); DataTable tmpTable = BaseSQL.Query(sqlStr).Tables[0]; for (int i = 0; i < tmpTable.Rows.Count; i++) { int wState = DataTypeConvert.GetInt(tmpTable.Rows[i]["WarehouseState"]); switch (wState) { case 1: if (checkNoApprover) { MessageHandler.ShowMessageBox(string.Format("预算外出库单[{0}]未审批,不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["SpecialWarehouseReceipt"]))); swwHeadTable.RejectChanges(); if (swwListTable != null) { swwListTable.RejectChanges(); } return(false); } break; case 2: if (checkApprover) { MessageHandler.ShowMessageBox(string.Format("预算外出库单[{0}]已经审批,不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["SpecialWarehouseReceipt"]))); swwHeadTable.RejectChanges(); if (swwListTable != null) { swwListTable.RejectChanges(); } return(false); } break; //case 3: // if (checkSettle) // { // MessageHandler.ShowMessageBox(string.Format("预算外出库单[{0}]已经结账,不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["SpecialWarehouseReceipt"]))); // swwHeadTable.RejectChanges(); // if (swwListTable != null) // swwListTable.RejectChanges(); // return false; // } // break; case 4: if (checkApproverBetween) { MessageHandler.ShowMessageBox(string.Format("预算外出库单[{0}]已经审批中,不可以操作。", DataTypeConvert.GetString(tmpTable.Rows[i]["SpecialWarehouseReceipt"]))); swwHeadTable.RejectChanges(); if (swwListTable != null) { swwListTable.RejectChanges(); } return(false); } break; } } return(true); }
/// <summary> /// 审批选中的多条预算外出库单 /// </summary> public bool SWRApprovalInfo_Multi(DataTable swwHeadTable, ref int successCountInt) { string swwHeadNoListStr = ""; for (int i = 0; i < swwHeadTable.Rows.Count; i++) { if (DataTypeConvert.GetBoolean(swwHeadTable.Rows[i]["Select"])) { swwHeadNoListStr += string.Format("'{0}',", DataTypeConvert.GetString(swwHeadTable.Rows[i]["SpecialWarehouseReceipt"])); } } swwHeadNoListStr = swwHeadNoListStr.Substring(0, swwHeadNoListStr.Length - 1); if (!CheckWarehouseState(swwHeadTable, null, swwHeadNoListStr, false, true, true, false)) { return(false); } successCountInt = 0; using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlCommand cmd = new SqlCommand("", conn, trans); DateTime serverTime = BaseSQL.GetServerDateTime(); for (int i = 0; i < swwHeadTable.Rows.Count; i++) { if (DataTypeConvert.GetBoolean(swwHeadTable.Rows[i]["Select"])) { DataRow swwRow = swwHeadTable.Rows[i]; string swwHeadNoStr = DataTypeConvert.GetString(swwRow["SpecialWarehouseReceipt"]); cmd.CommandText = string.Format("select INV_SpecialWarehouseReceiptHead.SpecialWarehouseReceipt, INV_SpecialWarehouseReceiptHead.ApprovalType, PUR_ApprovalType.ApprovalCat from INV_SpecialWarehouseReceiptHead left join PUR_ApprovalType on INV_SpecialWarehouseReceiptHead.ApprovalType = PUR_ApprovalType.TypeNo where SpecialWarehouseReceipt = '{0}'", swwHeadNoStr); DataTable tmpTable = new DataTable(); SqlDataAdapter orderadpt = new SqlDataAdapter(cmd); orderadpt.Fill(tmpTable); if (tmpTable.Rows.Count == 0) { trans.Rollback(); MessageHandler.ShowMessageBox("未查询到要操作的预算外出库单,请刷新后再进行操作。"); return(false); } ////审核检查出库明细数量是否超过采购订单明细数量 //DataTable orderListTable = new DataTable(); //QueryWarehouseReceiptList(orderListTable, swwHeadNoStr, false); //if (!CheckOrderApplyBeyondCount(cmd, swwHeadNoStr, orderListTable)) //{ // trans.Rollback(); // return false; //} //Set_OrderHead_End(cmd, orderListTable); string approvalTypeStr = DataTypeConvert.GetString(tmpTable.Rows[0]["ApprovalType"]); cmd.CommandText = string.Format("select * from F_OrderNoApprovalList('{0}','{1}') Order by AppSequence", swwHeadNoStr, approvalTypeStr); DataTable listTable = new DataTable(); SqlDataAdapter listadpt = new SqlDataAdapter(cmd); listadpt.Fill(listTable); if (listTable.Rows.Count == 0) { cmd.CommandText = string.Format("Update INV_SpecialWarehouseReceiptHead set WarehouseState = 2 where SpecialWarehouseReceipt='{0}'", swwHeadNoStr); cmd.ExecuteNonQuery(); swwHeadTable.Rows[i]["WarehouseState"] = 2; continue; } int approvalCatInt = DataTypeConvert.GetInt(tmpTable.Rows[0]["ApprovalCat"]); switch (approvalCatInt) { case 0: if (DataTypeConvert.GetInt(listTable.Rows[0]["Approver"]) != SystemInfo.user.AutoId) { continue; } break; case 1: case 2: if (listTable.Select(string.Format("Approver={0}", SystemInfo.user.AutoId)).Length == 0) { continue; } break; } cmd.CommandText = string.Format("Insert into PUR_OrderApprovalInfo(OrderHeadNo, Approver, ApproverTime) values ('{0}', {1}, '{2}')", swwHeadNoStr, SystemInfo.user.AutoId, serverTime.ToString("yyyy-MM-dd HH:mm:ss")); cmd.ExecuteNonQuery(); if (listTable.Rows.Count == 1 || approvalCatInt == 2) { cmd.CommandText = string.Format("Update INV_SpecialWarehouseReceiptHead set WarehouseState=2 where SpecialWarehouseReceipt='{0}'", swwHeadNoStr); cmd.ExecuteNonQuery(); swwHeadTable.Rows[i]["WarehouseState"] = 2; } else { cmd.CommandText = string.Format("Update INV_SpecialWarehouseReceiptHead set WarehouseState=4 where SpecialWarehouseReceipt='{0}'", swwHeadNoStr); cmd.ExecuteNonQuery(); swwHeadTable.Rows[i]["WarehouseState"] = 4; } //保存日志到日志表中 string logStr = LogHandler.RecordLog_OperateRow(cmd, "预算外出库单", swwHeadTable.Rows[i], "SpecialWarehouseReceipt", "审批", SystemInfo.user.EmpName, serverTime.ToString("yyyy-MM-dd HH:mm:ss")); if (DataTypeConvert.GetInt(swwHeadTable.Rows[i]["WarehouseState"]) == 2)//全部审核通过进行下一步操作 { SqlCommand cmd_proc = new SqlCommand("", conn, trans); string errorText = ""; if (!new FrmWarehouseNowInfoDAO().Update_WarehouseNowInfo(cmd_proc, swwHeadNoStr, 1, out errorText)) { trans.Rollback(); MessageHandler.ShowMessageBox("预算外出库单审核出库错误--" + errorText); return(false); } } successCountInt++; } } trans.Commit(); swwHeadTable.AcceptChanges(); return(true); } catch (Exception ex) { trans.Rollback(); swwHeadTable.RejectChanges(); throw ex; } finally { conn.Close(); } } } }
/// <summary> /// 打印处理 /// </summary> /// <param name="iaHeadNoStr">库存调整单号</param> /// <param name="handleTypeInt">打印处理类型:1 预览 2 打印 3 设计</param> public void PrintHandle(string iaHeadNoStr, int handleTypeInt) { DataSet ds = new DataSet(); DataTable headTable = BaseSQL.GetTableBySql(string.Format("select * from V_Prn_INV_InventoryAdjustmentsHead where InventoryAdjustmentsNo = '{0}' order by AutoId", iaHeadNoStr)); headTable.TableName = "InventoryAdjustmentsHead"; for (int i = 0; i < headTable.Columns.Count; i++) { #region 设定主表显示的标题 switch (headTable.Columns[i].ColumnName) { case "InventoryAdjustmentsNo": headTable.Columns[i].Caption = "调整单号"; break; case "InventoryAdjustmentsDate": headTable.Columns[i].Caption = "调整日期"; break; case "DepartmentNo": headTable.Columns[i].Caption = "部门编号"; break; case "DepartmentName": headTable.Columns[i].Caption = "部门名称"; break; case "RepertoryNo": headTable.Columns[i].Caption = "仓库编号"; break; case "RepertoryName": headTable.Columns[i].Caption = "仓库名称"; break; case "ProjectNo": headTable.Columns[i].Caption = "项目号"; break; case "ProjectName": headTable.Columns[i].Caption = "项目名称"; break; case "Remark": headTable.Columns[i].Caption = "备注"; break; case "Prepared": headTable.Columns[i].Caption = "制单人"; break; case "PreparedIp": headTable.Columns[i].Caption = "制单人IP"; break; case "Modifier": headTable.Columns[i].Caption = "修改人"; break; case "ModifierIp": headTable.Columns[i].Caption = "修改人IP"; break; case "ModifierTime": headTable.Columns[i].Caption = "修改时间"; break; } #endregion } ds.Tables.Add(headTable); DataTable listTable = BaseSQL.GetTableBySql(string.Format("select *, ROW_NUMBER() over (order by AutoId) as RowNum from V_Prn_INV_InventoryAdjustmentsList where InventoryAdjustmentsNo = '{0}' order by AutoId", iaHeadNoStr)); listTable.TableName = "InventoryAdjustmentsList"; for (int i = 0; i < listTable.Columns.Count; i++) { #region 设定子表显示的标题 switch (listTable.Columns[i].ColumnName) { case "RowNum": listTable.Columns[i].Caption = "行号"; break; case "InventoryAdjustmentsNo": listTable.Columns[i].Caption = "调整单号"; break; case "CodeNo": listTable.Columns[i].Caption = "物料编号"; break; case "CodeFileName": listTable.Columns[i].Caption = "零件编号"; break; case "CodeName": listTable.Columns[i].Caption = "零件名称"; break; case "CatgName": listTable.Columns[i].Caption = "分类名称"; break; case "CatgDescription": listTable.Columns[i].Caption = "分类说明"; break; case "CodeSpec": listTable.Columns[i].Caption = "规格型号"; break; case "CodeWeight": listTable.Columns[i].Caption = "重量"; break; case "MaterialVersion": listTable.Columns[i].Caption = "物料版本"; break; case "LibName": listTable.Columns[i].Caption = "Level 1"; break; case "MaterialCategory": listTable.Columns[i].Caption = "Level 2"; break; case "MaterialName": listTable.Columns[i].Caption = "Level 3"; break; case "Brand": listTable.Columns[i].Caption = "品牌"; break; case "FinishCatg": listTable.Columns[i].Caption = "表面处理"; break; case "LevelCatg": listTable.Columns[i].Caption = "加工等级"; break; case "Unit": listTable.Columns[i].Caption = "单位"; break; case "Qty": listTable.Columns[i].Caption = "调整数量"; break; case "Remark": listTable.Columns[i].Caption = "备注"; break; case "ShelfId": listTable.Columns[i].Caption = "货架ID"; break; case "ShelfNo": listTable.Columns[i].Caption = "货架号"; break; case "ShelfLocation": listTable.Columns[i].Caption = "货架位置"; break; } #endregion } ds.Tables.Add(listTable); ReportHandler rptHandler = new ReportHandler(); List <DevExpress.XtraReports.Parameters.Parameter> paralist = rptHandler.GetSystemInfo_ParamList(); rptHandler.XtraReport_Handle("INV_InventoryAdjustmentsHead", ds, paralist, handleTypeInt); }
public void Delete(int id) { BaseSQL.Execute(String.Format("DELETE FROM Patrimonios WHERE PatrimonioId = {0}", id)); }
/// <summary> /// 查询某个单据模板 /// </summary> public DataTable QueryDocTemplet(string tableNameStr) { string sqlStr = string.Format("select ModuleName, TableName, DocFileName, DocPath, DocVersion from BS_DocumentTemplet where TableName = '{0}'", tableNameStr); return(BaseSQL.GetTableBySql(sqlStr)); }
/// <summary> /// 窗体加载事件错误 /// </summary> private void FrmOrderListAndWarehouseWarrantList_Load(object sender, EventArgs e) { try { ControlCommonInit ctlInit = new ControlCommonInit(); DateTime nowDate = BaseSQL.GetServerDateTime(); dateOrderDateBegin.DateTime = nowDate.Date.AddDays(-SystemInfo.OrderQueryDate_DateIntervalDays); dateOrderDateEnd.DateTime = nowDate.Date; DataTable departmentTable_t = commonDAO.QueryDepartment(true); DataTable bussInfoTable_t = commonDAO.QueryBussinessBaseInfo(true); DataTable purCateTable_t = commonDAO.QueryPurCategory(true); lookUpReqDep.Properties.DataSource = departmentTable_t; lookUpReqDep.ItemIndex = 0; searchLookUpBussinessBaseNo.Properties.DataSource = bussInfoTable_t; searchLookUpBussinessBaseNo.Text = "全部"; lookUpPurCategory.Properties.DataSource = purCateTable_t; lookUpPurCategory.ItemIndex = 0; //searchLookUpProjectNo.Properties.DataSource = projectTable_t; //searchLookUpProjectNo.Text = "全部"; ctlInit.SearchLookUpEdit_ProjectList(searchLookUpProjectNo, true); searchLookUpProjectNo.Text = "全部"; //searchLookUpCodeFileName.Properties.DataSource = commonDAO.QueryPartsCode(true); //searchLookUpCodeFileName.EditValue = 0; ctlInit.SearchLookUpEdit_PartsCode(searchLookUpCodeFileName, true); searchLookUpCodeFileName.EditValue = 0; ctlInit.ComboBoxEdit_OrderState_Submit(comboBoxReqState); comboBoxReqState.SelectedIndex = 0; //repLookUpReqDep.DataSource = commonDAO.QueryDepartment(false); //repSearchBussinessBaseNo.DataSource = commonDAO.QueryBussinessBaseInfo(false); //repLookUpPurCategory.DataSource = commonDAO.QueryPurCategory(false); //repSearchProjectNo.DataSource = commonDAO.QueryProjectList(false); repLookUpReqDep.DataSource = departmentTable_t; repSearchBussinessBaseNo.DataSource = bussInfoTable_t; repLookUpPurCategory.DataSource = purCateTable_t; repSearchProjectNo.DataSource = searchLookUpProjectNo.Properties.DataSource; repLookUpRepertoryId.DataSource = commonDAO.QueryRepertoryInfo(false); repLookUpRepertoryLocationId.DataSource = commonDAO.QueryRepertoryLocationInfo(false); repSearchShelfId.DataSource = commonDAO.QueryShelfInfo(false); if (SystemInfo.DisableProjectNo) { labProjectNo.Visible = false; searchLookUpProjectNo.Visible = false; colProjectNo.Visible = false; colStnNo.Visible = false; } gridBottomOrderHead.pageRowCount = SystemInfo.OrderQueryGrid_PageRowCount; btnQuery_Click(null, null); } catch (Exception ex) { //ExceptionHandler.HandleException(this.Text + "--窗体加载事件错误。", ex); ExceptionHandler.HandleException(this.Text + "--" + f.tsmiCtjzsjcw.Text, ex); } }
/// <summary> /// 保存报价信息 /// </summary> public int SaveQuotationInfo(DataRow headRow, DataTable listTable) { using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlCommand cmd = new SqlCommand("", conn, trans); //DateTime nowTime = BaseSQL.GetServerDateTime(); if (headRow.RowState == DataRowState.Added)//新增 { headRow["AutoQuotationNo"] = BaseSQL.GetMaxCodeNo(cmd, "QN"); headRow["PreparedIp"] = SystemInfo.HostIpAddress; //headRow["RecordDate"] = nowTime; for (int i = 0; i < listTable.Rows.Count; i++) { listTable.Rows[i]["AutoQuotationNo"] = headRow["AutoQuotationNo"]; //listTable.Rows[i]["QuotationDate"] = nowTime; } } else//修改 { string autoQuotationNoStr = DataTypeConvert.GetString(headRow["AutoQuotationNo"]); if (!CheckState(headRow.Table, listTable, autoQuotationNoStr, false, true)) { return(-1); } if (CheckQuotationInfo_IsSalesOrder(cmd, autoQuotationNoStr)) { headRow.Table.RejectChanges(); listTable.RejectChanges(); return(-1); } //for (int i = 0; i < listTable.Rows.Count; i++) //{ // if (listTable.Rows[i].RowState == DataRowState.Deleted) // continue; // else if (listTable.Rows[i].RowState == DataRowState.Added) // { // listTable.Rows[i]["AutoQuotationNo"] = headRow["AutoQuotationNo"]; // listTable.Rows[i]["QuotationDate"] = nowTime; // } //} headRow["ModifierId"] = SystemInfo.user.AutoId; //headRow["Modifier"] = SystemInfo.user.EmpName; headRow["ModifierIp"] = SystemInfo.HostIpAddress; headRow["ModifierTime"] = BaseSQL.GetServerDateTime(); } //保存日志到日志表中 string logStr = LogHandler.RecordLog_DataRow(cmd, "报价信息", headRow, "AutoQuotationNo"); cmd.CommandText = "select * from SA_QuotationBaseInfo where 1=2"; SqlDataAdapter adapterHead = new SqlDataAdapter(cmd); DataTable tmpHeadTable = new DataTable(); adapterHead.Fill(tmpHeadTable); BaseSQL.UpdateDataTable(adapterHead, headRow.Table); cmd.CommandText = "select * from SA_QuotationPriceInfo where 1=2"; SqlDataAdapter adapterList = new SqlDataAdapter(cmd); DataTable tmpListTable = new DataTable(); adapterList.Fill(tmpListTable); BaseSQL.UpdateDataTable(adapterList, listTable); trans.Commit(); return(1); } catch (Exception ex) { trans.Rollback(); headRow.Table.RejectChanges(); listTable.RejectChanges(); throw ex; } finally { conn.Close(); } } } }
/// <summary> /// 打印处理 /// </summary> /// <param name="autoQuotationNoStr">报价单号</param> /// <param name="handleTypeInt">打印处理类型:1 预览 2 打印 3 设计</param> public void PrintHandle(string autoQuotationNoStr, int handleTypeInt) { DataSet ds = new DataSet(); DataTable headTable = BaseSQL.GetTableBySql(string.Format("select * from V_Prn_SA_QuotationBaseInfo where AutoQuotationNo = '{0}' order by AutoId", autoQuotationNoStr)); headTable.TableName = "QuotationBaseInfo"; for (int i = 0; i < headTable.Columns.Count; i++) { #region 设定主表显示的标题 switch (headTable.Columns[i].ColumnName) { case "AutoQuotationNo": headTable.Columns[i].Caption = "报价单号"; break; case "RFQNO": headTable.Columns[i].Caption = "手工单号"; break; case "RecordDate": headTable.Columns[i].Caption = "登记日期"; break; case "Requester": headTable.Columns[i].Caption = "客户需求人"; break; case "Remark": headTable.Columns[i].Caption = "备注"; break; case "BussinessBaseNo": headTable.Columns[i].Caption = "客户编号"; break; case "BussinessBaseText": headTable.Columns[i].Caption = "客户名称"; break; case "BussAddress": headTable.Columns[i].Caption = "客户公司地址"; break; case "BussPhoneNo": headTable.Columns[i].Caption = "客户电话"; break; case "BussFaxNo": headTable.Columns[i].Caption = "客户传真"; break; case "BussContact": headTable.Columns[i].Caption = "客户联系人"; break; case "ProjectName": headTable.Columns[i].Caption = "项目名称"; break; case "ParentAutoQuotationNo": headTable.Columns[i].Caption = "父级报价单号"; break; case "ParentAutoSalesOrderNo": headTable.Columns[i].Caption = "父级销售订单号"; break; case "ParentProjectNo": headTable.Columns[i].Caption = "父级项目号"; break; case "Prepared": headTable.Columns[i].Caption = "制单人"; break; case "PreparedIp": headTable.Columns[i].Caption = "制单人IP"; break; case "Modifier": headTable.Columns[i].Caption = "修改人"; break; case "ModifierIp": headTable.Columns[i].Caption = "修改人IP"; break; case "ModifierTime": headTable.Columns[i].Caption = "修改时间"; break; } #endregion } ds.Tables.Add(headTable); DataTable listTable = BaseSQL.GetTableBySql(string.Format("select *, ROW_NUMBER() over (order by AutoId) as RowNum from V_Prn_SA_QuotationPriceInfo where AutoQuotationNo = '{0}' order by AutoId", autoQuotationNoStr)); listTable.TableName = "QuotationPriceInfo"; for (int i = 0; i < listTable.Columns.Count; i++) { #region 设定子表显示的标题 switch (listTable.Columns[i].ColumnName) { case "RowNum": listTable.Columns[i].Caption = "行号"; break; case "AutoQuotationNo": listTable.Columns[i].Caption = "报价单号"; break; case "Amount": listTable.Columns[i].Caption = "金额"; break; case "Tax": listTable.Columns[i].Caption = "税率"; break; case "TaxAmount": listTable.Columns[i].Caption = "税额"; break; case "SumAmount": listTable.Columns[i].Caption = "价税合计"; break; case "Offerer": listTable.Columns[i].Caption = "报价人"; break; case "QuotationDate": listTable.Columns[i].Caption = "报价日期"; break; case "Versions": listTable.Columns[i].Caption = "版本"; break; case "Remark": listTable.Columns[i].Caption = "备注"; break; case "QuotationState": listTable.Columns[i].Caption = "单据状态"; break; case "StateDesc": listTable.Columns[i].Caption = "单据状态描述"; break; case "CurrencyCateAutoId": listTable.Columns[i].Caption = "币种ID"; break; case "CurrencyCateAbb": listTable.Columns[i].Caption = "币种缩写"; break; case "CurrencyCateName": listTable.Columns[i].Caption = "币种名称"; break; case "ExchangeRate": listTable.Columns[i].Caption = "汇率"; break; } #endregion } ds.Tables.Add(listTable); ReportHandler rptHandler = new ReportHandler(); List <DevExpress.XtraReports.Parameters.Parameter> paralist = rptHandler.GetSystemInfo_ParamList(); rptHandler.XtraReport_Handle("SA_QuotationBaseInfo", ds, paralist, handleTypeInt); }
/// <summary> /// 查询当前的报价基本信息 /// </summary> public void QueryQuotationBaseInfo_One(DataTable queryDataTable, int autoIdInt) { string sqlStr = string.Format("select top 1 * from SA_QuotationBaseInfo where AutoId = {0}", autoIdInt); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 按照报价单号查询报价单的COR信息 /// </summary> public void QueryQuotationCORInfo(DataTable queryDataTable, string autoQuotationNoStr) { string sqlStr = string.Format("select * from V_SA_QuotationCORInfo where ParentAutoQuotationNo = '{0}'", autoQuotationNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 查询报价单 /// </summary> public void QueryQuotationBaseInfo(DataTable queryDataTable, string beginDateStr, string endDateStr, string bussinessBaseNoStr, int creatorInt, string commonStr, bool nullTable) { string sqlStr = QueryQuotationBaseInfo_SQL(beginDateStr, endDateStr, bussinessBaseNoStr, creatorInt, commonStr, nullTable); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 查找数据库中最后一条报价基本信息 /// </summary> public void QueryQuotationBaseInfo_LastOne(DataTable queryDataTable) { string sqlStr = string.Format("select top 1 * from SA_QuotationBaseInfo order by AutoId desc"); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 按照报价单号查询报价基本信息 /// </summary> public void QueryQuotationBaseInfo(DataTable queryDataTable, string autoQuotationNoStr) { string sqlStr = string.Format("select top 1 * from SA_QuotationBaseInfo where AutoQuotationNo = '{0}'", autoQuotationNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
public TableColumnExpression(string tableName, BaseSQL sql) : base(tableName, sql) { }
/// <summary> /// 查询报价单明细的版本号 /// </summary> public DataTable Query_QuotationPriceInfo_Versions(string autoQuotationNoStr) { string sqlStr = string.Format("select Versions, Amount, QuotationDate, Offerer from SA_QuotationPriceInfo where AutoQuotationNo = '{0}'", autoQuotationNoStr); return(BaseSQL.GetTableBySql(sqlStr)); }
/// <summary> /// 查询单据模板表 /// </summary> /// <returns></returns> public DataTable QueryDocTemplet() { string sqlStr = "select AutoId, ParentId, ModuleName, TableName, DocFileName, DocPath, DocVersion from BS_DocumentTemplet order by AutoId"; return(BaseSQL.GetTableBySql(sqlStr)); }
/// <summary> /// 查询报价单版本的价格信息 /// </summary> public DataTable Query_Version_PriceInfo(string autoQuotationNoStr, string versionStr) { string sqlStr = string.Format("select AutoQuotationNo, Versions, Amount, Tax, TaxAmount, SumAmount from SA_QuotationPriceInfo where AutoQuotationNo = '{0}' and Versions = '{1}'", autoQuotationNoStr, versionStr); return(BaseSQL.GetTableBySql(sqlStr)); }
/// <summary> /// 取消隐藏选择的文件(取消隐藏标记) /// </summary> /// <param name="documentID"></param> public static void UpdateCancelHideFlag(int documentID) { string sql = "update a set a.hidden=0 from SA_Documents a where a.documentID =" + documentID; BaseSQL.ExecuteSql(sql); }
/// <summary> /// 查询出库单信息 /// </summary> public void QueryWarehouseReceiptHead(DataTable queryDataTable, string wrHeadNoStr) { string sqlStr = string.Format("select INV_WarehouseReceiptHead.*, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat from INV_WarehouseReceiptHead left join PUR_ApprovalType on INV_WarehouseReceiptHead.ApprovalType = PUR_ApprovalType.TypeNo where WarehouseReceipt = '{0}'", wrHeadNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 查询库存调整单表头表 /// </summary> /// <param name="queryDataTable">要查询填充的数据表</param> /// <param name="beginDateStr">开始日期字符串</param> /// <param name="endDateStr">结束日期字符串</param> /// <param name="adjustmentsRepertoryNoStr">调整仓库</param> /// <param name="adjustmentsProjectNoStr">调整项目号</param> /// <param name="reqDepStr">部门</param> /// <param name="preparedStr">制单人</param> /// <param name="commonStr">通用查询条件</param> /// <param name="nullTable">是否查询空表</param> public void QueryInventoryAdjustmentsHead(DataTable queryDataTable, string beginDateStr, string endDateStr, string adjustmentsRepertoryNoStr, string adjustmentsProjectNoStr, string reqDepStr, string preparedStr, string commonStr, bool nullTable) { BaseSQL.Query(QueryInventoryAdjustmentsHead_SQL(beginDateStr, endDateStr, adjustmentsRepertoryNoStr, adjustmentsProjectNoStr, reqDepStr, preparedStr, commonStr, nullTable), queryDataTable); }
/// <summary> /// 查询采购国内结账信息 /// </summary> public void QuerySettlementHead(DataTable queryDataTable, string settlementNoStr) { string sqlStr = string.Format("select PUR_SettlementHead.*, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat from PUR_SettlementHead left join PUR_ApprovalType on PUR_SettlementHead.ApprovalType = PUR_ApprovalType.TypeNo where SettlementNo = '{0}'", settlementNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 保存库存调整单 /// </summary> /// <param name="IAHeadRow">库存调整单表头数据表</param> /// <param name="IAListTable">库存调整单明细数据表</param> public int SaveInventoryAdjustments(DataRow IAHeadRow, DataTable IAListTable) { using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlCommand cmd = new SqlCommand("", conn, trans); ////检查当前库存数是否满足 //if (!CheckWarehouseNowInfoBeyondCount(cmd, DataTypeConvert.GetString(IAHeadRow["InventoryMoveNo"]), IAListTable)) //{ // return 0; //} if (DataTypeConvert.GetString(IAHeadRow["InventoryAdjustmentsNo"]) == "")//新增 { string iaNo = BaseSQL.GetMaxCodeNo(cmd, "IA"); IAHeadRow["InventoryAdjustmentsNo"] = iaNo; IAHeadRow["PreparedIp"] = SystemInfo.HostIpAddress; for (int i = 0; i < IAListTable.Rows.Count; i++) { IAListTable.Rows[i]["InventoryAdjustmentsNo"] = iaNo; IAListTable.Rows[i]["InventoryAdjustmentsDate"] = IAHeadRow["InventoryAdjustmentsDate"]; IAListTable.Rows[i]["AdjustmentsRepertoryNo"] = IAHeadRow["AdjustmentsRepertoryNo"]; IAListTable.Rows[i]["AdjustmentsProjectNo"] = IAHeadRow["AdjustmentsProjectNo"]; } } else//修改 { //if (!CheckWarehouseState(IMHeadRow.Table, IMListTable, string.Format("'{0}'", DataTypeConvert.GetString(IMHeadRow["SettlementNo"])), false, true, true, true)) // return -1; IAHeadRow["Modifier"] = SystemInfo.user.EmpName; IAHeadRow["ModifierIp"] = SystemInfo.HostIpAddress; IAHeadRow["ModifierTime"] = BaseSQL.GetServerDateTime(); for (int i = 0; i < IAListTable.Rows.Count; i++) { if (IAListTable.Rows[i].RowState == DataRowState.Deleted) { continue; } IAListTable.Rows[i]["InventoryAdjustmentsDate"] = IAHeadRow["InventoryAdjustmentsDate"]; IAListTable.Rows[i]["AdjustmentsRepertoryNo"] = IAHeadRow["AdjustmentsRepertoryNo"]; IAListTable.Rows[i]["AdjustmentsProjectNo"] = IAHeadRow["AdjustmentsProjectNo"]; } } //保存日志到日志表中 string logStr = LogHandler.RecordLog_DataRow(cmd, "库存调整单", IAHeadRow, "InventoryAdjustmentsNo"); cmd.CommandText = "select * from INV_InventoryAdjustmentsHead where 1=2"; SqlDataAdapter adapterHead = new SqlDataAdapter(cmd); DataTable tmpHeadTable = new DataTable(); adapterHead.Fill(tmpHeadTable); BaseSQL.UpdateDataTable(adapterHead, IAHeadRow.Table); cmd.CommandText = "select * from INV_InventoryAdjustmentsList where 1=2"; SqlDataAdapter adapterList = new SqlDataAdapter(cmd); DataTable tmpListTable = new DataTable(); adapterList.Fill(tmpListTable); BaseSQL.UpdateDataTable(adapterList, IAListTable); //Set_WWHead_End(cmd, IMListTable); trans.Commit(); return(1); } catch (Exception ex) { trans.Rollback(); IAHeadRow.Table.RejectChanges(); IAListTable.RejectChanges(); throw ex; } finally { conn.Close(); } } } }
/// <summary> /// 查询采购国内结账审批信息记录 /// </summary> public void QuerySettlementApprovalInfo(DataTable queryDataTable, string settlementNoStr, string typeNoStr) { string sqlStr = string.Format("select list.*, type.TypeNoText, BS_UserInfo.EmpName, BS_UserInfo.LoginId, info.SettlementNo , info.ApproverTime from PUR_ApprovalList as list left join PUR_ApprovalType as type on list.TypeNo = type.TypeNo left join BS_UserInfo on list.Approver = BS_UserInfo.AutoId left join PUR_SettlementApprovalInfo as info on list.Approver = info.Approver and info.SettlementNo = '{1}' where list.TypeNo = '{0}' order by AppSequence", typeNoStr, settlementNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 保存预算外出库单 /// </summary> /// <param name="swwHeadRow">预算外出库单表头数据表</param> /// <param name="swwListTable">预算外出库单明细数据表</param> public int SaveSpecialWarehouseReceipt(DataRow swwHeadRow, DataTable swwListTable) { using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlCommand cmd = new SqlCommand("", conn, trans); //if (!CheckOrderApplyBeyondCount(cmd, DataTypeConvert.GetString(wwHeadRow["WarehouseReceipt"]), wwListTable)) //{ // return 0; //} if (DataTypeConvert.GetString(swwHeadRow["SpecialWarehouseReceipt"]) == "")//新增 { string swwNo = BaseSQL.GetMaxCodeNo(cmd, "SR"); swwHeadRow["SpecialWarehouseReceipt"] = swwNo; swwHeadRow["PreparedIp"] = SystemInfo.HostIpAddress; for (int i = 0; i < swwListTable.Rows.Count; i++) { swwListTable.Rows[i]["SpecialWarehouseReceipt"] = swwNo; } } else//修改 { if (!CheckWarehouseState(swwHeadRow.Table, swwListTable, string.Format("'{0}'", DataTypeConvert.GetString(swwHeadRow["SpecialWarehouseReceipt"])), false, true, true, true)) { return(-1); } swwHeadRow["Modifier"] = SystemInfo.user.EmpName; swwHeadRow["ModifierIp"] = SystemInfo.HostIpAddress; swwHeadRow["ModifierTime"] = BaseSQL.GetServerDateTime(); } //保存日志到日志表中 string logStr = LogHandler.RecordLog_DataRow(cmd, "预算外出库单", swwHeadRow, "SpecialWarehouseReceipt"); cmd.CommandText = "select * from INV_SpecialWarehouseReceiptHead where 1=2"; SqlDataAdapter adapterHead = new SqlDataAdapter(cmd); DataTable tmpHeadTable = new DataTable(); adapterHead.Fill(tmpHeadTable); BaseSQL.UpdateDataTable(adapterHead, swwHeadRow.Table); cmd.CommandText = "select * from INV_SpecialWarehouseReceiptList where 1=2"; SqlDataAdapter adapterList = new SqlDataAdapter(cmd); DataTable tmpListTable = new DataTable(); adapterList.Fill(tmpListTable); BaseSQL.UpdateDataTable(adapterList, swwListTable); //Set_OrderHead_End(cmd, swwListTable); trans.Commit(); return(1); } catch (Exception ex) { trans.Rollback(); swwHeadRow.Table.RejectChanges(); swwListTable.RejectChanges(); throw ex; } finally { conn.Close(); } } } }
/// <summary> /// 查询预算外出库单信息 /// </summary> public void QuerySpecialWarehouseReceiptHead(DataTable queryDataTable, string specialWarehouseReceiptStr) { string sqlStr = string.Format("select Head.AutoId, Head.SpecialWarehouseReceipt as OrderHeadNo, Head.SpecialWarehouseReceiptDate as OrderHeadDate, Head.ReqDep, Head.WarehouseState as ReqState, Head.ApprovalType, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat, Head.SpecialWarehouseReceipt, Head.WarehouseState from INV_SpecialWarehouseReceiptHead as Head left join PUR_ApprovalType on Head.ApprovalType = PUR_ApprovalType.TypeNo where SpecialWarehouseReceipt = '{0}'", specialWarehouseReceiptStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 查询预算外出库单表头 /// </summary> /// <param name="queryDataTable">要查询填充的数据表</param> /// <param name="beginDateStr">开始日期字符串</param> /// <param name="endDateStr">结束日期字符串</param> /// <param name="repertoryNoStr">出库仓库编号</param> /// <param name="preparedStr">制单人</param> /// <param name="commonStr">通用查询条件</param> /// <param name="nullTable">是否查询空表</param> public void QuerySpecialWarehouseReceiptHead(DataTable queryDataTable, string beginDateStr, string endDateStr, string reqDepStr, string repertoryNoStr, int warehouseStateInt, string preparedStr, int approverInt, string commonStr, bool nullTable) { BaseSQL.Query(QuerySpecialWarehouseReceiptHead_SQL(beginDateStr, endDateStr, reqDepStr, repertoryNoStr, warehouseStateInt, preparedStr, approverInt, commonStr, nullTable), queryDataTable); }
/// <summary> /// 查询退货单信息 /// </summary> public void QueryReturnedGoodsReportHead(DataTable queryDataTable, string returnedGoodsReportNoStr) { string sqlStr = string.Format("select Head.AutoId, Head.ReturnedGoodsReportNo as OrderHeadNo, Head.ReturnedGoodsReportDate as OrderHeadDate, Head.ReqDep, Head.WarehouseState as ReqState, Head.ApprovalType, PUR_ApprovalType.TypeNoText, PUR_ApprovalType.ApprovalCat, Head.ReturnedGoodsReportNo, Head.WarehouseState from INV_ReturnedGoodsReportHead as Head left join PUR_ApprovalType on Head.ApprovalType = PUR_ApprovalType.TypeNo where ReturnedGoodsReportNo = '{0}'", returnedGoodsReportNoStr); BaseSQL.Query(sqlStr, queryDataTable); }
/// <summary> /// 取消审批选中的多条预算外出库单 /// </summary> public bool CancalSWRApprovalInfo_Multi(DataTable swwHeadTable) { string swwHeadNoListStr = ""; for (int i = 0; i < swwHeadTable.Rows.Count; i++) { if (DataTypeConvert.GetBoolean(swwHeadTable.Rows[i]["Select"])) { swwHeadNoListStr += string.Format("'{0}',", DataTypeConvert.GetString(swwHeadTable.Rows[i]["SpecialWarehouseReceipt"])); swwHeadTable.Rows[i]["WarehouseState"] = 1; } } swwHeadNoListStr = swwHeadNoListStr.Substring(0, swwHeadNoListStr.Length - 1); if (!CheckWarehouseState(swwHeadTable, null, swwHeadNoListStr, true, false, true, false)) { return(false); } using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlCommand cmd = new SqlCommand("", conn, trans); DateTime serverTime = BaseSQL.GetServerDateTime(); cmd.CommandText = string.Format("select SpecialWarehouseReceipt from INV_SpecialWarehouseReceiptHead where WarehouseState = 2 and SpecialWarehouseReceipt in ({0})", swwHeadNoListStr); DataTable approcalSWWTable = new DataTable(); SqlDataAdapter appradpt = new SqlDataAdapter(cmd); appradpt.Fill(approcalSWWTable); cmd.CommandText = string.Format("Delete from PUR_OrderApprovalInfo where OrderHeadNo in ({0})", swwHeadNoListStr); cmd.ExecuteNonQuery(); cmd.CommandText = string.Format("Update INV_SpecialWarehouseReceiptHead set WarehouseState=1 where SpecialWarehouseReceipt in ({0})", swwHeadNoListStr); cmd.ExecuteNonQuery(); //保存日志到日志表中 DataRow[] orderHeadRows = swwHeadTable.Select("select=1"); for (int i = 0; i < orderHeadRows.Length; i++) { ////检查是否有下级的主单 //if (CheckApplySettlement(cmd, DataTypeConvert.GetString(orderHeadRows[i]["WarehouseReceipt"]))) //{ // trans.Rollback(); // swwHeadTable.RejectChanges(); // MessageHandler.ShowMessageBox("出库单已经有适用的采购结账单记录,不可以操作。"); // return false; //} string logStr = LogHandler.RecordLog_OperateRow(cmd, "预算外出库单", orderHeadRows[i], "SpecialWarehouseReceipt", "取消审批", SystemInfo.user.EmpName, serverTime.ToString("yyyy-MM-dd HH:mm:ss")); } for (int i = 0; i < approcalSWWTable.Rows.Count; i++) { SqlCommand cmd_proc = new SqlCommand("", conn, trans); string errorText = ""; if (!new FrmWarehouseNowInfoDAO().Update_WarehouseNowInfo(cmd_proc, DataTypeConvert.GetString(approcalSWWTable.Rows[i]["SpecialWarehouseReceipt"]), 2, out errorText)) { trans.Rollback(); MessageHandler.ShowMessageBox("预算外出库单取消审核出库错误--" + errorText); return(false); } } trans.Commit(); swwHeadTable.AcceptChanges(); return(true); } catch (Exception ex) { trans.Rollback(); swwHeadTable.RejectChanges(); throw ex; } finally { conn.Close(); } } } }
/// <summary> /// 查询其他工位使用模块的数量 /// </summary> public int QueryOtherStnUseModuleCount(string smNoStr, int noContainId) { string sqlStr = string.Format("select COUNT(*) from SA_StnSummaryListModule where StnModuleId = '{0}' and AutoId not in ({1})", smNoStr, noContainId); return(DataTypeConvert.GetInt(BaseSQL.GetSingle(sqlStr))); }