/// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSave_Click(object sender, EventArgs e)
{
#region 验证
if (this.comboBoxReceiptTypeID.SelectedIndex == 0)
{
MessageBox.Show("请选择单据模板！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.checkBoxReceiptId.Checked == false)
{
MessageBox.Show("单据号必须选择！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
string strRTypeID = this.comboBoxReceiptTypeID.Text.Trim();
//插入前先全部删除
string strSqlDelete = "delete from T_ReceiptModCfg where RTypeID='{0}'";
strSqlDelete = string.Format(strSqlDelete, strRTypeID);
List <string> sqls = new List <string>();
sqls.Add(strSqlDelete);
Insert(this.listBoxMainTop, ref sqls, strRTypeID);
Insert(this.listBoxMainBottom, ref sqls, strRTypeID);
Insert(this.listBoxDetail, ref sqls, strRTypeID);
SqlDBConnect db = new SqlDBConnect();
db.Exec_Tansaction(sqls);
MessageBox.Show("保存成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
public void DeleteRela(DataGridView ddv)
{
SqlDBConnect sqldb = new SqlDBConnect();
int indexid = 0;
indexid = ddv.CurrentRow.Index;
string sqlcustid = "select CustID " +
"from T_CustomerInf where CustName='" + ddv.Rows[indexid].Cells[0].Value.ToString().Trim() + "'";
string custid = (new SqlDBConnect()).Ret_Single_val_Sql(sqlcustid);
string sqlparentcustid = "select CustID " +
"from T_CustomerInf where CustName='" + ddv.Rows[indexid].Cells[1].Value.ToString().Trim() + "'";
string parentcustid = (new SqlDBConnect()).Ret_Single_val_Sql(sqlparentcustid);
string sql = "delete from T_Customer_Rela where CustID='" + custid + "' and ParentID='" + parentcustid + "'";
try
{
sqldb.ExecuteNonQuery(sql);
MessageBox.Show("数据更新成功！");
ddv.Rows.RemoveAt(ddv.CurrentRow.Index);
ddv.Refresh();
}
catch
{ MessageBox.Show("数据更新失败！"); }
finally
{
}
}
/// <summary>
/// 展示全部图书数据
/// </summary>
/// <returns></returns>
public DataTable View()
{
SqlDBConnect db = new SqlDBConnect();
string sql1 = "select * FROM book";
return(db.GetDataTable(sql1));
}
/// <summary>
/// 获得RoleId表总的全部数据
/// </summary>
/// <returns></returns>
public static DataTable GetDatas()
{
string strSql = "select RoleId as 角色编号, RoleName as 角色名,Rmemo as 备注 from T_Roles";
SqlDBConnect db = new SqlDBConnect();
return(db.Get_Dt(strSql));
}
public static BookingRegistrationForm GetInstance(SqlDBConnect conn)
{
if (FormInstance == null)
{
FormInstance = new BookingRegistrationForm(conn);
}
return(FormInstance);
}
public void update_delete()
{
SqlDBConnect sqldbc = new SqlDBConnect();
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.Update(ds);
}
public static ClientRegistrationForm GetInstance(SqlDBConnect conn)
{
if (FormInstance == null)
{
FormInstance = new ClientRegistrationForm();
connection = conn;
}
return(FormInstance);
}
private BookingRegistrationForm(SqlDBConnect conn)
{
InitializeComponent();
_connection = conn;
if (_bookingManager == null)
{
_bookingManager = new BookingManager(conn);
}
}
public static DataTable GetDatas()
{
string strSql = "select T_Users.UserName as 人员, T_UsersView.UserName as 上级人员, T_UserType.UtypeName as 人员类型, T_UserGrade.Gname as 人员等级, T_UserRelation.CanRepair as 可维修机型 " +
"from T_UserRelation,T_Users,T_UsersView,T_UserType,T_UserGrade " +
"where T_UserRelation.Pid=T_Users.UserId and T_UserRelation.ParentPId=T_UsersView.UserId " +
"and T_UserRelation.Utype=T_UserType.TypeId and T_UserRelation.Gid=T_UserGrade.Gid";
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
public static DataTable GetDatasByParentPIdAndPid(string strParentPId, string strPid)
{
string strSql = "select * from T_UserRelation where ParentPId='{0}' and Pid='{1}'";
strSql = string.Format(strSql, strParentPId, strPid);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
public MainDroneForm()
{
InitializeComponent();
string password = StringConstants.PASSWORD;
string server = StringConstants.SERVER;
string database = StringConstants.DATABASE;
string uid = StringConstants.USER_ID;
string connectionString = StringConstants.CONNECTION_STRING;
connection = new SqlDBConnect(server, database, uid, password);
}
private void btnSave_Click(object sender, EventArgs e)
{
#region //验证
if (this.s_MainMenu.Text.Trim() == "")
{
MessageBox.Show("主菜单项不能为空！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.n_OrderIndex.Text.Trim() != "" && !Util.IsNumberic(this.n_OrderIndex))
{
MessageBox.Show("主顺序号输入有误，请输入数值！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (Util.LessOneNumber(this.n_OrderIndex, this.menuStripMain.Items.Count) == false)
{
MessageBox.Show("主顺序号只能输入小于等于" + this.menuStripMain.Items.Count + "的数字！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
SqlDBConnect db = new SqlDBConnect();
InitFuncs initFuncs = new InitFuncs();
DBUtil dbUtil = new DBUtil();
string strSql = "";
string strSql_ = "";
if (this.isAdd == true) //新增
{
strSql_ = "select * from T_MenuCfgMain where Ltrim(Rtrim(MainMenu))='{0}'";
strSql_ = string.Format(strSql_, this.s_MainMenu.Text.Trim());
//插入
strSql = initFuncs.Build_Insert_Sql(this.panelMenuMain, "T_MenuCfgMain");
}
else //修改
{
//更新
string strSqlWhere = "where Sysid='" + this.n_Sysid.Text.Trim() + "'";
strSql = initFuncs.Build_Update_Sql(this.panelMenuMain, "T_MenuCfgMain", strSqlWhere);
strSql_ = "select * from T_MenuCfgMain where Ltrim(Rtrim(Sysid)) != {0} and Ltrim(Rtrim(MainMenu))='{1}'";
strSql_ = string.Format(strSql_, this.n_Sysid.Text.Trim(), this.s_MainMenu.Text.Trim());
}
if (dbUtil.yn_exist_data(strSql_))
{
MessageBox.Show("该主菜单项已存在！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
db.ExecuteNonQuery(strSql);
MessageBox.Show("保存成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
InitDataGridView();
}
public static DataTable GetDatasByUserId(string userId)
{
string strSql = "select T_Users.UserName as 用户名,T_Roles.RoleName as 角色名 from T_Users,T_Roles,T_User_Role " +
"where T_User_Role.UserId = '{0}' and T_User_Role.UserId=T_Users.UserId and T_User_Role.RoleId=T_Roles.RoleId";
strSql = string.Format(strSql, userId);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
private void button1_Click(object sender, EventArgs e)
{
SqlDBConnect cn = new SqlDBConnect();
string sq = "";
for (int i = 0; i < textBox2.Lines.Count(); i++)
{
sq = "select T_SelItems values(" + textBox1.Text + textBox2.Lines[i] + ")";
}
List <string> list = new List <string>();
list.Add(sq);
cn.Exec_Tansaction(list);
}
public static bool IsExistData(string tableName, string sqlWhere)
{
string strSql = "select * from {0} {1}";
strSql = string.Format(strSql, tableName, sqlWhere);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
if (dt.Rows.Count <= 0)
{
return(false);
}
return(true);
}
public List <EmployeeDetails> GetAllRecord()
{
List <EmployeeDetails> objReturn = null;
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
objReturn = db.Query <EmployeeDetails>("udp_EmployeeDetails_lst", commandType: System.Data.CommandType.StoredProcedure).ToList();
}
}
catch (Exception ex)
{ log.Error("GetAllRecorod Error: ", ex); }
return(objReturn);
}
/// <summary>
/// 从服务器读取执行文件的上传时间
/// </summary>
/// <returns></returns>
public string get_version()
{
string vernew_ = "";
string sql_ = "select UploadTime from T_SysConfig";
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(sql_);
if (dt == null || dt.Rows.Count <= 0)
{
return("");
}
vernew_ = dt.Rows[0]["UploadTime"].ToString().Trim();
return(vernew_);
}
public static DataTable GetDatasByParentPId(string strParentPId)
{
string strSql = "select T_Users.UserName as 下级人员, T_UserType.UtypeName as 人员类型, T_UserGrade.Gname as 人员等级, T_UserRelation.CanRepair as 可维修机型 " +
"from T_UserRelation inner join T_Users " +
"on T_UserRelation.ParentPId='{0}' and T_UserRelation.Pid=T_Users.UserId left outer join T_UserType " +
"on T_UserRelation.Utype=T_UserType.TypeId left outer join T_UserGrade " +
"on T_UserRelation.Gid=T_UserGrade.Gid";
strSql = string.Format(strSql, strParentPId);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
return(dt);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOK_Click(object sender, EventArgs e)
{
//以中文字段名显示
//Return_Sql = "select * from " + tableName + " where "; //英文字段名
//for (int i = 0; i < lb_sql.Items.Count; i++)
//{
// Return_Sql += " " + lb_sql.Items[i].ToString().Trim();
//}
Return_Sql = "";
if (lb_sql.Items.Count > 0)
{
if (this.strSql.Contains(" where "))
{
Return_Sql += this.strSql + " and ";
}
else
{
Return_Sql += this.strSql + " where ";
}
for (int i = 0; i < lb_sql.Items.Count; i++)
{
Return_Sql += " " + lb_sql.Items[i].ToString().Trim();
}
}
else
{
Return_Sql = this.strSql;
}
try
{
SqlDBConnect db = new SqlDBConnect();
DataTable dt = new DataTable();
dt = db.Get_Dt(Return_Sql);
if (dt == null || dt.Rows.Count <= 0)
{
MessageBox.Show("没有符合条件的记录存在！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
InitFuncs initFuncs = new InitFuncs();
initFuncs.InitDataGridView(this.dataGridView, dt);
this.Height = 450;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString() + "\n" + Return_Sql, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void TestForm_Load(object sender, EventArgs e)
{
string strSql = "select T_CustomerInf.CustID,T_CustomerInf.CustName,T_CustomerInf.communicateAddr,T_CustContacts.Tel " +
"from T_CustomerInf inner join T_CustContacts " +
"on T_CustomerInf.CustID='{0}' and T_CustomerInf.CustID=T_CustContacts.CustID";
strSql = string.Format(strSql, "1");
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
(new InitFuncs()).InitDataGridView(this.dataGridView1, dt);
this.dataGridView1.SelectionMode = DataGridViewSelectionMode.CellSelect;
this.progressBar1.Minimum = 0;
}
static void Main(string[] args)
{
string password = "******";
string server = "localhost";
string database = "sys";
string uid = "root";
string connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD="******";";
SqlDBConnect connection = new SqlDBConnect(server, database, uid, password);
var selectQuery = "SELECT * FROM sys.client WHERE(clientName='Glen')";
connection.Select(selectQuery, 1);
Console.ReadKey();
}
public Guid?InsertUpdateRecord(EmployeeDetails objEmployeeDetails)
{
Guid?objReturn = null;
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
objReturn = db.Query <Guid>("udp_EmployeeDetails_ups", param: objEmployeeDetails, commandType: System.Data.CommandType.StoredProcedure).Single();
}
}
catch (Exception ex)
{
log.Error("InsertUpdateRecord Error: ", ex);
}
return(objReturn);
}
public Guid InsertUpdateRecord(Admin objAdmin)
{
Guid objReturn = new Guid();
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
objReturn = db.Query <Guid>("udp_Admin_ups", commandType: System.Data.CommandType.StoredProcedure).Single();
}
}
catch (Exception ex)
{
log.Error("InsertUpdateRecord Error: ", ex);
}
return(objReturn);
}
public EmployeeDetails GetRecordById(Guid iId)
{
EmployeeDetails objReturn = null;
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
DynamicParameters param = new DynamicParameters();
param.Add("@Id", iId);
objReturn = db.Query <EmployeeDetails>("udp_EmployeeDetails_sel", param: param, commandType: System.Data.CommandType.StoredProcedure).SingleOrDefault();
}
}
catch (Exception ex)
{ log.Error("GetRecordById Error: ", ex); }
return(objReturn);
}
public bool DeleteRecord(int iId)
{
bool objReturn = false;
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
DynamicParameters param = new DynamicParameters();
param.Add("@Id", iId);
db.Query("udp_EmployeeDetails_del", param: param, commandType: System.Data.CommandType.StoredProcedure);
objReturn = true;
}
}
catch (Exception ex)
{ log.Error("DeleteRecord Error: ", ex); }
return(objReturn);
}
/// <summary>
/// 保存
/// </summary>
private void btnSave_Click(object sender, EventArgs e)
{
//输入数据有效性验证
if (s_AttachName.Text.Trim() == "")
{
MessageBox.Show("附件名称不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string strSqlSel = "";
string strSql = "";
DBUtil dbUtil = new DBUtil();
SqlDBConnect db = new SqlDBConnect();
if (this.Type == "add")
{
//判断该资料原件记录是否已存在
strSqlSel = "select * from T_BargAttach where barsysid={0} and AttachName='{1}'";
strSqlSel = string.Format(strSqlSel, this.barsysid, this.s_AttachName.Text.Trim());
strSql = (new InitFuncs()).Build_Insert_Sql(this.panel1, "T_BargAttach");
}
else if (this.Type == "edit")
{
int SysId = Convert.ToInt32(this.dataGridView1.SelectedRows[0].Cells["附件编号"].Value.ToString().Trim());
strSqlSel = "select * from T_BargAttach where BarSysId like {0} and Ltrim(Rtrim(AttachName)) like '{1}' and Ltrim(Rtrim(SysId)) not like {2}";
strSqlSel = string.Format(strSqlSel, this.barsysid, this.s_AttachName.Text.Trim(), SysId);
string strWhere = "where SysId=" + SysId;
strSql = (new InitFuncs()).Build_Update_Sql(this.panel1, "T_BargAttach", strWhere);
}
bool isExist = dbUtil.yn_exist_data(strSqlSel);
if (isExist)
{
MessageBox.Show("当前合同已存在该附件名称！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
db.ExecuteNonQuery(strSql);
//MessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
initdataGridview();
}
/// <summary>
/// 获得物料 成本单价 (移动平均法，出库)
/// </summary>
private double CalculateCostPriceOut(string matId, int matType, string storeHouseId)
{
SqlDBConnect db = new SqlDBConnect();
double costPrice = 0.0;//最终返回的 物料的 成本单价
string maxBalanceTime = StockStatusDAO.GetBalanceTime();
string strSql = "select LastCount,LastCost,BalanceTime from T_Stock_Status " +
"where StoreHouseId='{0}' and MatId='{1}' and MatType={2} and BalanceTime='{3}'";
strSql = string.Format(strSql, storeHouseId, matId, matType, maxBalanceTime);
DataTable dt = db.Get_Dt(strSql);
double lastCost = 0.0; //取最近的一次结存的 成本单价
string BalanceTime = "190001"; //取最近的一次结存的 结存时间
if (dt != null && dt.Rows.Count > 0)
{
//取最近的一次结存的 成本单价
lastCost = Convert.ToDouble(dt.Rows[0]["LastCost"].ToString().Trim());
BalanceTime = dt.Rows[0]["BalanceTime"].ToString().Trim();
}
string strSqlSel = "select T_Receipts_Det.ReceiptId,STaxPurchPrice,CurAveragePrice from T_Receipts_Det,T_Receipt_Main,T_ReceiptModal " +
"where T_ReceiptModal.ReceTypeID=T_Receipt_Main.ReceiptTypeID and T_ReceiptModal.InOrOutBound='入库' and " +
"T_Receipt_Main.SourceStoreH='{0}' and T_Receipt_Main.CurWorkMonth > '{1}' and " +
"T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and " +
"T_Receipts_Det.MatId='{2}' and T_Receipts_Det.MatType={3}";
strSqlSel = string.Format(strSqlSel, storeHouseId, BalanceTime, matId, matType);
DataTable dtDet = db.Get_Dt(strSqlSel);
if (dtDet != null && dtDet.Rows.Count > 0)
{
costPrice = Convert.ToDouble(dtDet.Rows[dtDet.Rows.Count - 1]["CurAveragePrice"].ToString().Trim());//该仓库该物料该类型最后一次移动平均价
}
else
{
costPrice = lastCost;
}
return(costPrice);
}
public static List <string> GetFunctionById(string roleId)
{
List <string> listFunctions = new List <string>();
string strSql = "select [Function] from T_Role_Rights where RoleId='{0}'";
strSql = string.Format(strSql, roleId);
SqlDBConnect db = new SqlDBConnect();
DataTable dt = db.Get_Dt(strSql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
listFunctions.Add(dr["Function"].ToString().Trim());
}
}
return(listFunctions);
}
public EmployeeDetailsList GetRecordPage(int iPageNo, int iPageSize)
{
EmployeeDetailsList objReturn = new EmployeeDetailsList();
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
DynamicParameters param = new DynamicParameters();
param.Add("@pageNum", iPageNo);
param.Add("@pageSize", iPageSize);
param.Add("@TotalRecords", dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.Output);
objReturn.EmployeeDetailss = db.Query <EmployeeDetails>("udp_EmployeeDetails_lstpage", param, commandType: System.Data.CommandType.StoredProcedure).ToList();
objReturn.TotalRecords = param.Get <int>("@TotalRecords");
}
}
catch (Exception ex)
{ log.Error("GetRecordsPage Error: ", ex); }
return(objReturn);
}
public Admin Validate(string email, string password)
{
Admin objReturn = null;
try
{
using (SqlConnection db = new SqlDBConnect().GetConnection())
{
DynamicParameters param = new DynamicParameters();
param.Add("@Email", email);
param.Add("@PasswordHash", password);
objReturn = db.Query <Admin>("udp_ValidateAdmin", param: param, commandType: System.Data.CommandType.StoredProcedure).SingleOrDefault();
}
}
catch (Exception ex)
{
log.Error("Validate Error: ", ex);
}
return(objReturn);
}