public static SqlDataReader GetUserLogin(string sOpCode, string sPassword)
{
///执行SQL
string SqlText = "SELECT a.*,b.RoleName FROM SysUser_Info a "
+ " left join SysRole_Info b on a.RoleID=b.ID "//and IsNull(b.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " WHERE a.OpCode='" + sOpCode + "' AND a.Password='******' AND a.Status=0 And IsNull(a.IsCanLogin,1)=1";
//+ " And IsNull(a.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString();
return(DataCommon.GetDataByReader(SqlText));
}
//获取车间列表
public static DataSet GetToolsNoticeLstByDataSet(string WhereSQL)
{
string sSQL = "Select a.*,IsNull(b.SelfClickNum, 0) As SelfClickNum,c.OpName from Notice_Info a "
+ " left join (Select MasterID, Sum(ClickNum) As SelfClickNum From SysClick_Info Where ClickUserID=" + SysClass.SysGlobal.GetCurrentUserID().ToString()
+ " And MasterType=" + cs_Notice_MasterType + " Group by MasterID) b on b.MasterID=a.Id"
+ " left join Sysuser_info c on a.CreateUserID=c.ID"
+ " Where a.Status=0" + WhereSQL;
sSQL = sSQL + " Order By a.Createdtime desc,a.SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
public static DataSet GetPurchaseCreditLstByDataSet(string sWhereSQL)
{
string sSQL = "Select a.*, b.OrganName from PurchaseCredit_Info a "
+ " Left join SysOrgan_Info b on b.Status=0 and b.ID=a.OrganID"
+ " Where a.Status=0"
+ sWhereSQL;
sSQL = sSQL + " Order By a.PurchaseYear Desc, a.OrganID";
return(DataCommon.GetDataByDataSet(sSQL));
}
public static SqlDataReader GetSingleUserSalaryByReader(int _UserSalaryID)
{
string sSQL = "";
sSQL = "select b.OpName, b.Sex, b.IdNumber, a.*"
+ " from UserSalary_Info a "
+ " left join SysUser_Info b on b.Status=0 And a.OpCode=b.OpCode"
+ " Where a.Status=0 And a.ID=" + _UserSalaryID.ToString();
return(DataCommon.GetDataByReader(sSQL));
}
//登录日志
public static int CreateUserLogin(int UserID, string OpCode, string OpName, string LogText, string IP, string ComputerName)
{
string sSQL = "begin Insert into SysLogs_Info (LogType, OpCode, OpName, LogText,IP,ComputerName) "
+ " Values('登录系统','" + OpCode + "','" + OpName + "','" + LogText + "','" + IP + "','" + ComputerName + "');";
sSQL = sSQL + " Update SysUser_Info Set IsError=1 Where ID=" + UserID.ToString() + " And LastIp<>'" + IP + "';";
sSQL = sSQL + " Update SysUser_Info Set IsError=0 Where ID=" + UserID.ToString() + " And LastIp='" + IP + "';";
sSQL = sSQL + " Update SysUser_Info Set LastDate=GetDate(), LastIp='" + IP + "', LastComputerName='" + ComputerName + "' Where ID=" + UserID.ToString() + ";";
sSQL = sSQL + " End;";
return(DataCommon.QueryData(sSQL));
}
//获取单个车间信息
public static int DeleteSinglePackTools(int _IDs)
{
string sSQL = "begin Delete from Tool_Info Where Status=0 And ID in (" + _IDs.ToString() + "); ";
sSQL = sSQL + " Delete from ToolMember_Info Where Status=0 And PackToolID in (" + _IDs.ToString() + ");";
sSQL += " Delete From ToolStock_Info Where ToolID in (" + _IDs.ToString() + ");";
string sLogText = "删除 系统管理>人员管理 机构部门：ID:" + _IDs.ToString() + "的记录。";
sSQL = sSQL + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
return(DataCommon.QueryData(sSQL));
}
public static DataSet GetPackToolsLstByDataSet(string WhereSQL)
{
string sSQL = "Select a.*, IsNull(b.OrganName,'') As OrganName from Tool_Info a "
+ " Left join SysOrgan_Info b on b.Status=0 And a.OrganID=b.ID"
+ " Where IsNull(a.ToolType,0)=1 And a.Status=0" + WhereSQL;
sSQL = sSQL + " Order By a.SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
/// <summary>
/// Updates a Property record and returns the number of records affected
/// </summary>
public static int Update(PropertyDO DO)
{
SqlParameter _PropertyId = new SqlParameter("PropertyId", SqlDbType.Int);
SqlParameter _PageId = new SqlParameter("PageId", SqlDbType.Int);
SqlParameter _ArticleHtml = new SqlParameter("ArticleHtml", SqlDbType.VarChar);
SqlParameter _Lat = new SqlParameter("Lat", SqlDbType.VarChar);
SqlParameter _Long = new SqlParameter("Long", SqlDbType.VarChar);
SqlParameter _PhotoUrl = new SqlParameter("PhotoUrl", SqlDbType.VarChar);
SqlParameter _PhotoCaption = new SqlParameter("PhotoCaption", SqlDbType.VarChar);
SqlParameter _Price = new SqlParameter("Price", SqlDbType.VarChar);
SqlParameter _SoldDate = new SqlParameter("SoldDate", SqlDbType.VarChar);
SqlParameter _Zestimate = new SqlParameter("Zestimate", SqlDbType.VarChar);
SqlParameter _PriceSQFt = new SqlParameter("PriceSQFt", SqlDbType.VarChar);
SqlParameter _Address = new SqlParameter("Address", SqlDbType.VarChar);
SqlParameter _PropertyData = new SqlParameter("PropertyData", SqlDbType.VarChar);
SqlParameter _LotSize = new SqlParameter("LotSize", SqlDbType.VarChar);
SqlParameter _YearBuilt = new SqlParameter("YearBuilt", SqlDbType.VarChar);
_PropertyId.Value = DO.PropertyId;
_PageId.Value = DO.PageId;
_ArticleHtml.Value = DO.ArticleHtml;
_Lat.Value = DO.Lat;
_Long.Value = DO.Long;
_PhotoUrl.Value = DO.PhotoUrl;
_PhotoCaption.Value = DO.PhotoCaption;
_Price.Value = DO.Price;
_SoldDate.Value = DO.SoldDate;
_Zestimate.Value = DO.Zestimate;
_PriceSQFt.Value = DO.PriceSQFt;
_Address.Value = DO.Address;
_PropertyData.Value = DO.PropertyData;
_LotSize.Value = DO.LotSize;
_YearBuilt.Value = DO.YearBuilt;
SqlParameter[] _params = new SqlParameter[] {
_PropertyId,
_PageId,
_ArticleHtml,
_Lat,
_Long,
_PhotoUrl,
_PhotoCaption,
_Price,
_SoldDate,
_Zestimate,
_PriceSQFt,
_Address,
_PropertyData,
_LotSize,
_YearBuilt
};
return(DataCommon.ExecuteScalar("[dbo].[Property_Update]", _params, "dbo"));
}
public static SqlDataReader GetReturnListinfoBorrow(string WhereSQL)
{
string sSQL = "select a.BrandNames,b.ConsumeDate,e.SerialNum,f.Specification from WorkBorrowuserDetail_Info a "
+ "left join WorkBorrowUser_Info b on a.ApplyListID=b.ID left join SysUser_Info c on b.ConsumeUserID=c.ID "
+ "left join SysOrgan_Info d on d.ID=c.OrganID "
+ "left join Interphone_Info e on a.InterID=e.ID "
+ "left join Tool_Info f on e.ModeID=f.ID"
+ " where a.Status=0 and a.IsReturn=1 " + WhereSQL;
return(DataCommon.GetDataByReader(sSQL));
}
public static SqlDataReader GetReturnInsListInfo(string WhereSQL)
{
string sSQL = "select a.*,b.ConsumeNo,c.SerialNum,d.Specification,d.AliasesName,e.OrganName, f.OpName,b.ConsumeDate from InterPhoneInsectionDetail_Info a "
+ " left join InterPhoneInspection_Info b on a.ApplyListID=b.ID "
+ " left join Interphone_Info c on a.InterID=c.ID left join Tool_Info d on c.ModeID=D.ID "
+ " left join SysOrgan_Info e on c.OrgainID=e.ID "
+ " left join SysUser_Info f on b.ConsumeUserID=f.ID "
+ " where b.ApprovalStatus=1 And a.IsReturn=1" + WhereSQL;
return(DataCommon.GetDataByReader(sSQL));
}
//select
public static DataSet GetAllinter(int modeID, int OrganID)
{
string Ssql = "select a.*,b.ConsumeNo,c.SerialNum,d.Specification,d.AliasesName,e.OrganName from WorkInterCarryDetail_Info a left join WorkInterCarry_Info b on a.ApplyListID=b.ID left join Interphone_Info c on a.InterID=c.ID left join Tool_Info d on c.ModeID=D.ID left join SysOrgan_Info e on c.OrgainID=e.ID where b.ApprovalStatus=1 and a.IsReturn=1 ";
if (modeID > 0)
{
Ssql = Ssql + " and c.ModeID=" + modeID + "";
}
Ssql = Ssql + Ssql;
return(DataCommon.GetDataByDataSet(Ssql));
}
public static SqlDataReader GetSysCustomByReader(int OrganID)
{
string sSQL = "SELECT a.*,b.OrganName"
+ " FROM SysCustomField_Info a "
+ " left join SysOrgan_Info b on a.OrganID=b.ID and b.Status=0 "
+ " WHERE a.Status=0";
sSQL = sSQL + " And a.OrganID=" + OrganID.ToString();
sSQL = sSQL + " Order By a.SortID";
return(DataCommon.GetDataByReader(sSQL));
}
//无线电台送修单列表
public static DataSet GetInspectionListByset(string sql)
{
string SSql = "select a.* ,b.OpName,e.OrganName,case a.ApprovalStatus when 0 then '草稿' when 1 then '送修成功'end as a "
+ " from InterPhoneCarry_Info a "
+ " left join SysUser_Info b on a.ConsumeUserID=b.ID"
+ " left join SysOrgan_Info e on a.ServiceID=e.ID"
+ " where a.status=0 ";
SSql = SSql + sql;
SSql = SSql + "order by a.ConsumeDate desc";
return(DataCommon.GetDataByDataSet(SSql));
}
public static string GetUserNotice(string userID, string userOrganID)
{
string sSQL = "Select a.*,IsNull(b.SelfClickNum, 0) As SelfClickNum,c.OpName from Notice_Info a "
+ " left join (Select MasterID, Sum(ClickNum) As SelfClickNum From SysClick_Info Where ClickUserID="
+ userID + " Group by MasterID) b on b.MasterID=a.Id"
+ " left join Sysuser_info c on a.CreateUserID=c.ID"
+ " Where a.Status=0" + " And a.OrganID in (select ID from [GetParentOrganByID](" + userOrganID +
"))" + " Order By a.Createdtime desc,a.SortID";
DataSet ds = DataCommon.GetDataByDataSet(sSQL);
return(DataToJson(ds.Tables[0]));
}
public static SqlDataReader GetSingUserImportRecByID(int _UserSalaryID)
{
string sSQL = "";
sSQL = "select a.*"
+ " from UserUserImportRec_Info a "
+ " Where a.Status=0 And a.ID=" + _UserSalaryID;
sSQL = sSQL + " Order By SalaryDate Desc";
return(DataCommon.GetDataByReader(sSQL));
}
public static int GetOrganIDByOrganName(string _OrganName)
{
int _UserID = 0;
SqlDataReader sdr = DataCommon.GetDataByReader("Select ID From SysOrgan_Info Where OrganName='" + _OrganName + "'");
if (sdr.Read())
{
_UserID = int.Parse(sdr["ID"].ToString());
}
sdr.Close();
return(_UserID);
}
public static int GetCustomIDByOpCode(string _OpCode)
{
int _CustomID = 0;
SqlDataReader sdr = DataCommon.GetDataByReader("Select ID From SysCustomField_Info Where OpCode='" + _OpCode + "'");
if (sdr.Read())
{
_CustomID = int.Parse(sdr["ID"].ToString());
}
sdr.Close();
return(_CustomID);
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (PeopleGridView.CurrentRow != null)
{
if (MessageBox.Show("Are you sure you want to delete this row?", "PeopleGridView", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
DataGridViewRow dr = PeopleGridView.CurrentRow;
DataCommon.PeopleDelete(dr);
bindingSource1.DataSource = DataCommon.LoadPeople();
}
}
}
public static string GetCustomNameByOpCode(string _OpCode)
{
string _CustomName = "";
SqlDataReader sdr = DataCommon.GetDataByReader("Select OpName From SysCustomField_Info Where OpCode='" + _OpCode + "'");
if (sdr.Read())
{
_CustomName = sdr["OpName"].ToString();
}
sdr.Close();
return(_CustomName);
}
public static int GetTopBaseMainID()
{
int _TopMapID = 0;
SqlDataReader sdr = DataCommon.GetDataByReader("Select ID From SysBaseMain_Info Where Status=0 Order By SortID");
if (sdr.Read())
{
_TopMapID = int.Parse(sdr["ID"].ToString());
}
sdr.Close();
return(_TopMapID);
}
public static DataSet GetUserSalarySetLstByDataSet(string sWhereSQL)
{
string sSQL = "";
sSQL = "select a.*"
+ " from UserSalarySet_Info a "
+ " Where a.Status=0 " + sWhereSQL;
sSQL = sSQL + " Order By a.BeginYears Desc";
return(DataCommon.GetDataByDataSet(sSQL));
}
//获取地图列表首个ID
public static int GetTopNoticeID(int PNoticeID)
{
int _TopMapID = 0;
SqlDataReader sdr = DataCommon.GetDataByReader("Select ID From Notice_Info Where Status=0 and PNoticeID=" + PNoticeID.ToString() + " Order By NoticeNo, SortID");
if (sdr.Read())
{
_TopMapID = int.Parse(sdr["ID"].ToString());
}
sdr.Close();
return(_TopMapID);
}
public static DataSet GetUserImportRecLstByDataSet(string sWhereSQL)
{
string sSQL = "";
sSQL = "select a.*"
+ " from UserUserImportRec_Info a "
+ " Where a.Status=0 " + sWhereSQL;
sSQL = sSQL + " Order By SalaryDate Desc";
return(DataCommon.GetDataByDataSet(sSQL));
}
/// <summary>
/// Deletes a Page record
/// </summary>
public static int Delete(PageDO DO)
{
SqlParameter _PageId = new SqlParameter("PageId", SqlDbType.Int);
_PageId.Value = DO.PageId;
SqlParameter[] _params = new SqlParameter[] {
_PageId
};
return(DataCommon.ExecuteScalar("[dbo].[Page_Delete]", _params, "dbo"));
}
// ===↑
public static bool GetExecSqlIsExist(string ExecSQL)
{
bool bFlag = false;
SqlDataReader recc = DataCommon.GetDataByReader(ExecSQL);
if (recc.Read())
{
bFlag = true;
}
recc.Close();
return(bFlag);
}
//-----------------
public static SqlDataReader QueryDetailLst()
{
//int iRecCount = 1;
//if (count > 0)
//{
// iRecCount = count;
//}
//string sSQL = "Select Top " + iRecCount + " * from SysLogs_Info Where LogType Like '%" + prefixText + "%' and Status=0";
string sSQL = "select LogType,count(id) from SysLogs_Info group by LogType";//分组，去除重复数据
return(DataCommon.GetDataByReader(sSQL));
}
public void UpdateOnExcel(InsureCarData newItem)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE MA_INSURE_CAR SET ");
sql.Append(" COMPANY_CODE = N'" + newItem.COMPANY_CODE + "',");
sql.Append(" PACKAGE_NAME = N'" + newItem.PACKAGE_NAME + "',");
sql.Append(" CAR_ID = '" + newItem.CAR_ID + "',");
sql.Append(" INSURE_CATEGORY = N'" + newItem.INSURE_CATEGORY + "',");
//sql.Append(" INSURE_TYPE_REPAIR = '" + newItem.INSURE_TYPE_REPAIR + "',");
sql.Append(" CAR_YEAR = N'" + newItem.CAR_YEAR + "',");
sql.Append(" LIVE_COVERAGE_PEOPLE = '" + newItem.LIVE_COVERAGE_PEOPLE + "',");
sql.Append(" LIVE_COVERAGE_TIME = '" + newItem.LIVE_COVERAGE_TIME + "',");
sql.Append(" ASSET_TIME = '" + newItem.ASSET_TIME + "',");
sql.Append(" INSURE_TYPE_REPAIR = '" + newItem.INSURE_TYPE_REPAIR + "',");
sql.Append(" DAMAGE_TO_VEHICLE = '" + newItem.DAMAGE_TO_VEHICLE + "',");
sql.Append(" MISSING_FIRE_CAR = '" + newItem.MISSING_FIRE_CAR + "',");
sql.Append(" FIRST_DAMAGE_PRICE = '" + newItem.FIRST_DAMAGE_PRICE + "',");
sql.Append(" PERSONAL_ACCIDENT_AMT = '" + newItem.PERSONAL_ACCIDENT_AMT + "',");
sql.Append(" PERSONAL_ACCIDENT_PEOPLE = '" + newItem.PERSONAL_ACCIDENT_PEOPLE + "',");
sql.Append(" MEDICAL_FEE_AMT = '" + newItem.MEDICAL_FEE_AMT + "',");
sql.Append(" MEDICAL_FEE_PEOPLE = '" + newItem.MEDICAL_FEE_PEOPLE + "',");
sql.Append(" DRIVER_INSURANCE_AMT = '" + newItem.DRIVER_INSURANCE_AMT + "',");
sql.Append(" NET_PRICE = '" + newItem.NET_PRICE + "',");
sql.Append(" TOTAL_PRICE = '" + newItem.TOTAL_PRICE + "',");
sql.Append(" PRICE_ROUND = '" + newItem.PRICE_ROUND + "',");
sql.Append(" CAPITAL_INSURANCE = '" + newItem.CAPITAL_INSURANCE + "',");
sql.Append(" INSURE_PRIORITY = '" + newItem.INSURE_PRIORITY + "',");
sql.Append(" EFFECTIVE_DATE = '" + ConvertCommon.ConvertDateTime(newItem.EFFECTIVE_DATE) + "',");
sql.Append(" EXPIRE_DATE = '" + ConvertCommon.ConvertDateTime(newItem.EXPIRE_DATE) + "',");
sql.Append(" CONFIDENTIAL_STATUS = '" + newItem.CONFIDENTIAL_STATUS + "',");
sql.Append(" UPDATE_DATE = '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',");
sql.Append(" UPDATE_USER = '******'");
sql.Append(" WHERE COMPANY_CODE = '" + newItem.COMPANY_CODE + "'");
sql.Append(" AND PACKAGE_NAME = '"+ newItem.PACKAGE_NAME + "'");
sql.Append(" AND CAR_ID = '"+ newItem.CAR_ID + "'");
sql.Append(" AND INSURE_CATEGORY = '"+ newItem.INSURE_CATEGORY + "'");
sql.Append(" AND INSURE_TYPE_REPAIR = '"+ newItem.INSURE_TYPE_REPAIR + "'");
sql.Append(" AND CAR_YEAR = '"+ newItem.CAR_YEAR + "'");
sql.Append(" AND DAMAGE_TO_VEHICLE = '" + newItem.DAMAGE_TO_VEHICLE + "'");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (Exception ex)
{
throw ex;
}
}
//查询送检库存
public static DataSet GetInStockBySet(int CategoryID, string sWhereSQL)
{
string Ssql = "select a.*,b.ConsumeNo,c.SerialNum,d.Specification,d.AliasesName,e.OrganName from InterPhoneCarryDetail_Info "
+ " a left join InterPhoneCarry_Info b on a.ApplyListID=b.ID left join Interphone_Info c on a.InterID=c.ID left join Tool_Info d on c.ModeID=D.ID left join SysOrgan_Info e on c.OrgainID=e.ID where b.ApprovalStatus=1 and a.IsReturn=1 ";
if (CategoryID > 0)
{
Ssql = Ssql + " and c.ModeID=" + CategoryID + "";
}
Ssql = Ssql + sWhereSQL;
return(DataCommon.GetDataByDataSet(Ssql));
}
public static SqlDataReader GetWhereToolLstByReader(int CategoryID, string WhereSQL)
{
string sSQL = "Select a.* from Tool_Info a Where a.ToolType=" + _NomalToolType.ToString() + " And a.Status=0" + WhereSQL;
if (CategoryID > 0)
{
sSQL = sSQL + " And a.CategoryID=" + CategoryID.ToString();
}
sSQL = sSQL + " Order By a.SortID";
return(DataCommon.GetDataByReader(sSQL));
}
public static DataSet GetToolStockDetailByDataSet(int OrganID, int ToolID, string sWhereSQL)
{
string sSQL = " select a.ToolID, a.ToolCode, a.TestCode, a.NextTestDate, c.StorageLocation, a.ToolDate"
+ ", (Case a.ToolStatus when 0 then '库存' when 1 then '领用' when 2 then '借用'"
+ " when 3 then '送检' when 4 then '送修' when 5 then '注销' when 5 then '注销' when 6 then '损益' when 7 then '发货' when 9 then '调出' end) as ToolStatusName"
+ " ,b.*, a.ID as ToolDetailID from ToolStockDetail_Info a"
+ " left join Tool_Info b on a.ToolID=b.ID"
+ " left join ToolStock_Info c on a.ToolID=c.ToolID And c.OrganID=" + OrganID.ToString()
+ " Where a.OrganID=" + OrganID.ToString() + " And a.ToolID=" + ToolID + sWhereSQL;
return(DataCommon.GetDataByDataSet(sSQL));
}
/// <summary>
/// Switch system language
/// </summary>
/// <param name="languageType"></param>
public void SwitchLanguage(DataCommon.CommonResource.LANGUAGE languageType)
{
//set system language
string langType = string.Empty;
try
{
this.SystemLanguage = languageType;
if (this.SystemLanguage == DataCommon.CommonResource.LANGUAGE.English)
{
CommonResource.CurrentSystemLanguage = CommonResource.LANGUAGE.English;
langType = "en-US";
}
else if (this.SystemLanguage == DataCommon.CommonResource.LANGUAGE.Japanese)
{
CommonResource.CurrentSystemLanguage = CommonResource.LANGUAGE.Japanese;
langType = "ja-JP";
}
else if (this.SystemLanguage == DataCommon.CommonResource.LANGUAGE.Chinese)
{
CommonResource.CurrentSystemLanguage = CommonResource.LANGUAGE.Chinese;
langType = "zh-Hans";
}
}
catch
{
langType = string.Empty;
}
if (!string.IsNullOrEmpty(langType))
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(langType);
System.Threading.Thread.CurrentThread.CurrentUICulture = System.Globalization.CultureInfo.CreateSpecificCulture(langType);
}
else { System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture; }
}
