public List <RateTypeEntity> SelectAllRateTypeEntity()
{
string sqlstr = "select * from dbo.RateType";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToRateTypeEntityList(ds));
}
public List <StorageWayEntity> SelectAllStorageWayEntity()
{
string sqlstr = "select * from dbo.StorageWay";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToStorageWayEntityList(ds));
}
public List <DepotsPropertyEntity> SelectAllDepotsPropertyEntity()
{
string sqlstr = "select * from dbo.DepotsProperty";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToDepotsPropertyEntityList(ds));
}
public List <BanDoc> List_SelectDocGia()
{
List <BanDoc> list = new List <BanDoc>();
try
{
dt = dh.ExecuteDataSet("usp_Reader_Select", new SqlParameter[0]).Tables[0];
if (dt.Rows.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
BanDoc sl = new BanDoc
{
ID = dr["ID"] == DBNull.Value ? "" : dr["ID"].ToString(),
Name = dr["NAME"] == DBNull.Value ? "" : dr["NAME"].ToString(),
DateCreate = dr["DATECREATE"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dr["DATECREATE"].ToString()),
PhoneNumber = dr["PHONENUMBER"] == DBNull.Value ? "" : dr["PHONENUMBER"].ToString(),
UUID = dr["UUID"] == DBNull.Value ? "" : dr["UUID"].ToString(),
};
list.Add(sl);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static DataSet GetAllItemGroup()
{
//string ProcName = "sp_Manage_ITEMGROUP";
List <ReportEntity> lstData = new List <ReportEntity>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[dbo].[sp_Manage_ITEMGROUP]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@Mode", "T");
helper.Parameters.Add("@ItemRefID", 0);
helper.Parameters.Add("@pk_GroupID", 0);
helper.Parameters.Add("@UserID", 1);
helper.Parameters.Add("@Result", 0);
helper.Open();
return(helper.ExecuteDataSet());
//helper.ExecuteReader(CommandBehavior.CloseConnection);
//while (helper.DataReader.Read())
//{
// ReportEntity report = new ReportEntity();
// report.LoadCashierWiseSale(helper.DataReader);
// lstData.Add(report);
//}
//helper.Close();
}
}
public List <SelectUser> List_SelectUser()
{
List <SelectUser> list = new List <SelectUser>();
try
{
dt = dh.ExecuteDataSet("usp_NhanVien_select", new SqlParameter[0]).Tables[0];
if (dt.Rows.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SelectUser sl = new SelectUser
{
ID = dr["ID"] == DBNull.Value ? "" : dr["ID"].ToString(),
Name = dr["NAME"] == DBNull.Value ? "" : dr["NAME"].ToString(),
Username = dr["USERNAME"] == DBNull.Value ? "" : dr["USERNAME"].ToString(),
IsAdmin = dr["ISADMIN"] == DBNull.Value ? false : bool.Parse(dr["ISADMIN"].ToString()),
};
list.Add(sl);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public List <MenuEntity> SelectMenuEntity()
{
string sqlstr = "select * from dbo.Menu";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToMenuList(ds));
}
public DataSet SelectStocktakingDetails(long stocktakingId)
{
string sqlstr = $" select * from Operator where Ope_id = {stocktakingId} ";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(ds);
}
public List <SkuAuxType> SelectSkuAuxTypeEntityList()
{
string sqlstr = "select * from SkuAuxType ";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToSignTypeEntityList(ds));
}
public List <NhaXuatBanOBJ> List_Select()
{
List <NhaXuatBanOBJ> list = new List <NhaXuatBanOBJ>();
try
{
dt = dh.ExecuteDataSet("usp_NXB_Select", new SqlParameter[0]).Tables[0];
if (dt.Rows.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
NhaXuatBanOBJ sl = new NhaXuatBanOBJ
{
ID = dr["ID"] == DBNull.Value ? "" : dr["ID"].ToString(),
Name = dr["NAME"] == DBNull.Value ? "" : dr["NAME"].ToString(),
};
list.Add(sl);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public List <OrganizationEntity> SelectAllOrganizationEntity()
{
string sqlstr = "SELECT con.Cmst_ID,con.Cmst_Name,con.Cmst_IfUse,op.Ope_Account from dbo.CmstOrganization AS con LEFT JOIN Operator AS op ON con.Cmst_ID = op.Ope_Cmst_ID WHERE op.Ope_IfSysAccount = 1";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToOrganizationEntityList(ds));
}
public List <Subject> SelectAllSubjectEntityList()
{
string sqlstr = "select * from Subject ";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToSubjectEntityList(ds));
}
public List <BillType> SelectAllBillTypeEntity()
{
string sqlstr = "select * from BillType ";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToBillTypeEntity(ds));
}
public MenuEntity SelectMenuEntity(int menuID)
{
string sqlstr = "select * from dbo.Menu where Meu_ID={0}";
sqlstr = string.Format(sqlstr, menuID);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToMenuEntity(ds));
}
public OperationEntity SelectOperationEntity(int operationID)
{
string sqlstr = "select * from dbo.Operation where Ope_ID={0}";
sqlstr = string.Format(sqlstr, operationID);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToOperationEntity(ds));
}
public OrganizationEntity SelectOrganizationByID(int cmstID)
{
string sqlstr = "select * from dbo.CmstOrganization where Cmst_ID={0}";
sqlstr = string.Format(sqlstr, cmstID);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToOrganizationEntity(ds));
}
public DataSet SelectOrganizationByCmstName(string cmstName)
{
string sqlstr = "select * from dbo.CmstOrganization where Cmst_Name='{0}'";
sqlstr = string.Format(sqlstr, cmstName);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(ds);
}
public bool CheckedOperatorAccountRepeate(string account, int operatorId)
{
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), "Operator_View_Check", account, operatorId);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(true);
}
return(false);
}
public StockModel SelectStocktakingBill(int cmstId, long Id, SqlTransaction st)
{
string sqlstr = $" select * from StocktakingView where StackingBillID = {Id} and CmstID = {cmstId} ";
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlstr);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(DrToStocktakingBill(ds.Tables[0].Rows[0]));
}
return(null);
}
public DataSet SelectAllMenu()
{
string sqlstr = "select * from dbo.Menu";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(ds);
}
return(null);
}
public int SelectStocktakingStatus(long stocktakingId, int status, SqlTransaction st)
{
string sqlstr = $" select Count(Sb_Id) from StocktakingBill where Sb_Id = {stocktakingId} and Sb_Status = {status} ";
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlstr);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
int ret = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
if (ret > 0)
{
return(ret);
}
}
return(-1);
}
public static DataTable GetUserSpecificMenuList(int UserID)
{
DataSet ds = new DataSet();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUserSpecificMenuList]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", UserID);
helper.Open();
ds = helper.ExecuteDataSet();
helper.Close();
}
return(ds.Tables[0]);
}
public static DataSet GetUserById(int userId)
{
DataSet ds = new DataSet();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUserById]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Open();
ds = helper.ExecuteDataSet();
helper.Close();
}
return(ds);
}
public int SelectStocktakingBillDetaisCount(long stocktakingId, int status, SqlTransaction st)
{
StringBuilder sqlsb = new StringBuilder($" select count(Sbd_Id) from StocktakingBillDetail where Sbd_sb_id = {stocktakingId} ");
if (status > 0)
{
sqlsb.Append($" and Sbd_Status = {status} ");
}
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlsb.ToString());
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(Convert.ToInt32(ds.Tables[0].Rows[0][0]));
}
return(-1);
}
public DepotsPropertyEntity SelectDepotsPropertyByName(string name)
{
string sqlstr = "select * from dbo.DepotsProperty where Dpr_name='{0}'";
sqlstr = String.Format(sqlstr, name);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ds.Tables[0].Rows.Count > 0)
{
return(DsToDepotsPropertyEntity(ds));
}
else
{
return(null);
}
}
public RateTypeEntity SelectRateTypeByName(string name)
{
string sqlstr = "select * from dbo.RateType where Rty_Name='{0}'";
sqlstr = String.Format(sqlstr, name);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ds.Tables[0].Rows.Count > 0)
{
return(DsToRateTypeEntity(ds));
}
else
{
return(null);
}
}
public long InsertStocktakingBill(StockModel sb, SqlTransaction st)
{
MessageBox.Show("山映斜阳天接水，芳草无情，更在斜阳外！！！");
string sqlstr = $" insert into StocktakingBill ( Sb_Cmst_ID, Sb_Dep_ID, Sb_Rar_Id, Sb_Com_ID, Sb_Ope_Maker, Sb_MakeTime, Sb_Status) values ({sb.CmstID},{(sb.DepotID == null ? "NULL" : sb.DepotID.ToString())},{(sb.ReservoirID == null ? "NULL" : sb.ReservoirID.ToString())},{(sb.CompanyID == null ? "NULL" : sb.CompanyID.ToString())},{sb.Maker},getdate(),{sb.Status});select @@Identity;";
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlstr);
long ret = -1;
if (DataValidate.CheckDataSetNotEmpty(ds))
{
ret = Convert.ToInt64(ds.Tables[0].Rows[0][0] ?? -1);
return(ret);
}
else
{
throw new Exception(INSERT_STOCKTAKINGBILL_ERROR);
}
}
public DataTable Select_login(string user, string pass)
{
DataTable dt;
try
{
SqlParameter[] pa = new SqlParameter[]
{
new SqlParameter("@Username", user),
new SqlParameter("@Password", pass),
};
dt = dh.ExecuteDataSet("usp_Login", pa).Tables[0];
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public StocktakingBillDetail SelectStocktakingBillDetail(long stocktakingDetailId, SqlTransaction st = null)
{
string sqlstr = $" select * from StocktakingDetailInfo where StocktakingDetailID = {stocktakingDetailId} ";
DataSet ds;
if (st == null)
{
ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
}
else
{
ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlstr);
}
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(DrToStocktakingBillDetail(ds.Tables[0].Rows[0]));
}
return(null);
}
public RoleEntity InsertRoleEtity(RoleEntity re)
{
try
{
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), "Role_Insert", re.CmstID, re.CsyID, re.RoleName, re.Remark, re.IfUse == true ? 1 : 0);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
re.RoleID = Convert.ToInt32(ds.Tables[0].Rows[0]["Rol_ID"]);
return(re);
}
else
{
throw new Exception("插入角色失败");
}
}
catch (Exception ex)
{
throw ex;
}
}
