public static UserInfo CheckUser(string txtUserID, string txtPassword, string strLanuage)
{
string sqlString = "select a.*,b.DepartmentName,c.RoleNo,d.RoleName " +
"from BS_UserInfo a left join BS_Department b on a.DepartmentNo=b.DepartmentNo " +
"left join BS_RoleUser c on a.AutoID=c.UserNo " + //autoID是UserInfo表的关键字
"left join BS_Role d on c.RoleNo=d.RoleNo " +
"where a.LoginID='" + txtUserID + "' and a.LoginPwd='" + txtPassword + "'";
DataSet ds = new DataSet();
ds = BaseSQL.Query(sqlString);
if (ds.Tables[0].Rows.Count > 0)
{
SystemInfo.user = new UserInfo();
SystemInfo.user.DepartmentNo = ds.Tables[0].Rows[0]["DepartmentNo"].ToString();
SystemInfo.user.EmpName = ds.Tables[0].Rows[0]["EmpName"].ToString();
SystemInfo.user.LoginID = ds.Tables[0].Rows[0]["LoginID"].ToString();
SystemInfo.user.DepartmentName = ds.Tables[0].Rows[0]["DepartmentNo"].ToString();
SystemInfo.user.RoleNo = ds.Tables[0].Rows[0]["RoleNo"].ToString();
SystemInfo.user.RoleName = ds.Tables[0].Rows[0]["RoleName"].ToString();
SystemInfo.user.AutoId = (int)ds.Tables[0].Rows[0]["AutoId"];
SystemInfo.user.ButtonPower = DataTypeConvert.GetInt(ds.Tables[0].Rows[0]["ButtonPower"]);
SystemInfo.user.Lanuage = strLanuage;
SystemInfo.user.IsDisable = DataTypeConvert.GetInt(ds.Tables[0].Rows[0]["IsDisable"]);
return(SystemInfo.user);
}
else
{
return(SystemInfo.user);
}
}
/// <summary>
/// 获得子表对应数据
/// </summary>
/// <param name="dataSet1">要填充的数据集</param>
/// <param name="gdvBussinessBaseInfo">主表gridControl</param>
public static void GetChildTableData(DataSet dataSet1, DevExpress.XtraGrid.Views.Grid.GridView gdvBussinessBaseInfo)
{
dataSet1.Tables[1].Clear();
string strSql = "select * from BS_BussinessDetailInfo where BussinessBaseNo like '" + gdvBussinessBaseInfo.GetRowCellValue(gdvBussinessBaseInfo.GetSelectedRows()[0], "BussinessBaseNo").ToString() + "'";
BaseSQL.Query(strSql, dataSet1.Tables["BussinessDetailInfo"]);
dataSet1.Tables[2].Clear();
strSql = "select * from BS_BussinessFinancialInfo where BussinessBaseNo like '" + gdvBussinessBaseInfo.GetRowCellValue(gdvBussinessBaseInfo.GetSelectedRows()[0], "BussinessBaseNo").ToString() + "'";
BaseSQL.Query(strSql, dataSet1.Tables["BussinessFinancialInfo"]);
}
/// <summary>
/// 查询Bom子表信息
/// </summary>
public void QueryBomMateriel(DataTable queryDataTable, string materielNoStr)
{
string sqlStr = " 1=1";
//if (materielNoStr != "")
{
sqlStr += string.Format(" and MaterielNo = '{0}'", materielNoStr);
}
sqlStr = string.Format("select bom.*, SW_PartsCode.CodeName as LevelMaterielName from BS_BomMateriel as bom left join SW_PartsCode on bom.LevelMaterielNo = SW_PartsCode.CodeFileName where {0} order by AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询Bom主表单条信息
/// </summary>
public void QueryBomManagement_Single(DataTable queryDataTable, string materielNoStr)
{
string sqlStr = " 1=1";
//if (materielNoStr != "")
{
sqlStr += string.Format(" and MaterielNo = '{0}'", materielNoStr);
}
sqlStr = string.Format("select BS_BomManagement.*, SW_PartsCode.CodeName from BS_BomManagement left join SW_PartsCode on BS_BomManagement.MaterielNo = SW_PartsCode.CodeFileName where {0} order by BS_BomManagement.AutoId", sqlStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询的全部数据存为Excel文件
/// </summary>
/// <param name="QueryTableContainer">查询数据的容器</param>
/// <param name="sqlStr">查询全部数据的SQL</param>
/// <param name="displayGridView">显示数据的GridView容器</param>
public void SaveExcel_QueryAllData(DataTable QueryTableContainer, string sqlStr, GridView displayGridView)
{
displayGridView.GridControl.Visible = false;
DataTable tempTable = QueryTableContainer.Copy();
QueryTableContainer.Rows.Clear();
BaseSQL.Query(sqlStr, QueryTableContainer);
FileHandler.SaveDevGridControlExportToExcel(displayGridView);
QueryTableContainer.Rows.Clear();
foreach (DataRow dr in tempTable.Rows)
{
QueryTableContainer.ImportRow(dr);
}
displayGridView.GridControl.Visible = true;
}
public static ArrayList strSqlLlist = new ArrayList();//存储SQL语句
/// <summary>
/// 初始化
/// </summary>
/// <param name="ds"></param>
public static void InitBussinessCategory(DataSet ds)
{
BaseSQL.Query("select * from BS_BussinessCategory", ds.Tables["BussinessCategory"]);
}
///// <summary>
///// 查询用户信息
///// </summary>
//public DataTable QueryUserInfo(bool addAllItem)
//{
// string sqlStr = "select BS_UserInfo.AutoId, EmpName, BS_UserInfo.DepartmentNo, DepartmentName from BS_UserInfo left join BS_Department on BS_UserInfo.DepartmentNo = BS_Department.DepartmentNo";
// if (addAllItem)
// {
// sqlStr = "select 0 as AutoId, '全部' as EmpName, '' as DepartmentNo, '' as DepartmentName union " + sqlStr;
// }
// return BaseSQL.GetTableBySql(sqlStr);
//}
public void QueryUserInfo(DataTable queryDataTable)
{
string sqlStr = "select BS_UserInfo.AutoId, EmpName, BS_UserInfo.DepartmentNo, DepartmentName from BS_UserInfo left join BS_Department on BS_UserInfo.DepartmentNo = BS_Department.DepartmentNo order by BS_UserInfo.AutoId";
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询Bom的树形更多信息
/// </summary>
public DataTable QueryBomTreeList_MoreInfo(string materielNoStr)
{
string sqlStr = string.Format("select bom.*, SW_PartsCode.CodeName, SW_PartsCode.AutoId as PCAutoId, SW_PartsCode.FilePath, SW_PartsCode.Brand, SW_PartsCode.CatgName, SW_PartsCode.CodeSpec, SW_PartsCode.Unit, BS_BomMaterieState.MaterieStateText from F_BomMateriel_TreeRelation('{0}') as bom left join SW_PartsCode on bom.CodeFileName = SW_PartsCode.CodeFileName left join BS_BomManagement on bom.CodeFileName = BS_BomManagement.MaterielNo left join BS_BomMaterieState on BS_BomMaterieState.MaterieStateId = BS_BomManagement.MaterieStateId Order by CodeFileName", materielNoStr);
return(BaseSQL.Query(sqlStr).Tables[0]);
}
/// <summary>
/// 查询Bom的树形基本信息
/// </summary>
public DataTable QueryBomTreeList_BaseInfo(string materielNoStr)
{
string sqlStr = string.Format("select bom.*, SW_PartsCode.CodeName, SW_PartsCode.AutoId as PCAutoId from F_BomMateriel_TreeRelation('{0}') as bom left join SW_PartsCode on bom.CodeFileName = SW_PartsCode.CodeFileName Order by CodeFileName", materielNoStr);
return(BaseSQL.Query(sqlStr).Tables[0]);
}
/// <summary>
/// 查询往来方金融信息
/// </summary>
public void QueryBussinessFinancialInfo(DataTable queryDataTable, string bussinessBaseNoStr)
{
string sqlStr = string.Format("select * from BS_BussinessFinancialInfo where BussinessBaseNo = '{0}'", bussinessBaseNoStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 初始化往来方主表
/// </summary>
public static void InitBussinessBaseInfo(DataSet ds)
{
BaseSQL.Query("select * from BS_BussinessBaseInfo", ds.Tables["BussinessBaseInfo"]);
}
/// <summary>
/// 查询付款类型信息
/// </summary>
public void QueryCollectionTypeList(DataTable queryDataTable, string collectionTypeNoStr)
{
string sqlStr = string.Format("select * from BS_CollectionTypeList where CollectionTypeNo='{0}'", collectionTypeNoStr);
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询系统参数表
/// </summary>
public void QuerySystemParameter(DataTable queryDataTable)
{
string sqlStr = "select * from BS_SysParameter";
BaseSQL.Query(sqlStr, queryDataTable);
}
/// <summary>
/// 查询公司信息表
/// </summary>
public void QueryCompanyInfo(DataTable queryDataTable)
{
string sqlStr = "select * from BS_CompanyInfo";
BaseSQL.Query(sqlStr, queryDataTable);
}