/// <summary> /// 从System.Data.DataTable导入数据到数据库 /// </summary> /// <param name="dt"></param> /// <returns></returns> private int InsertData(DataTable dt) { int i = 0; string cmonth = dateTimePickerMonth.Text; string mat_code = ""; string customer = ""; string product_model = ""; decimal standard_hours = 0; ConnDB conn = new ConnDB(); bool err = false; foreach (DataRow dr in dt.Rows) { if (dr["新物料编码"].ToString().Trim() == null || dr["新物料编码"].ToString().Trim() == "") { MessageBox.Show("新物料编码格式有错误!"); err = true; break; } } if (!err) { foreach (DataRow dr in dt.Rows) { mat_code = dr["新物料编码"].ToString().Trim(); customer = dr["客户"].ToString().Trim(); product_model = dr["产品型号"].ToString().Trim(); standard_hours = Common.StrToDecimal(dr["标准单机工时"].ToString().Trim()); string sql = string.Format("Insert into COST_HH_STANDARD_HOURS(CMONTH,MAT_CODE,CUSTOMER,PRODUCT_MODEL,STANDARD_HOURS) Values ('{0}','{1}','{2}','{3}',{4})", cmonth, mat_code, customer, product_model, standard_hours); conn.EditDatabase(sql); i++; } } return(i); }
private bool Import(string file, string sheet) { ConnDB conn = new ConnDB(); bool success = true; int i = 0; try { DataTable dt; dt = GetDataFromExcel(file, sheet); i = UpdateData(dt); } catch { MessageBox.Show("失败!"); success = false; } if (success && i > 0) { MessageBox.Show("导入成功!"); } conn.Close(); return(success); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string sql = "update cost_position set cname = ltrim(rtrim('" + textEditName.Text.ToString() + "')),person_type_id =" + comboBoxPersonType.SelectedValue.ToString(); sql = sql + " where cid = " + textEditID.Text.ToString(); string sql2 = "select cname from cost_Position where cname = '" + textEditName.Text.ToString().Trim() + "' and person_type_id = " + comboBoxPersonType.SelectedValue.ToString(); if (textEditName.Text.ToString().Trim() != "" && comboBoxPersonType.SelectedValue.ToString() != "0") { int rows = conn.ReturnRecordCount(sql2); if (rows > 0) { MessageBox.Show("该职位已经存在!"); } else { bool isok = conn.EditDatabase(sql); if (isok) { MessageBox.Show("修改成功!"); PositionQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("不能为空值!"); } conn.Close(); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string sql, sql2; string month = dateTimePickerMonth.Text.ToString(); int rows; sql2 = "select cmonth from COST_TEMP_CLOSE where cmonth ='" + month + "'"; rows = conn.ReturnRecordCount(sql2); if (rows > 0) { sql = "update i set i.closed = 1 from COST_TEMP_CLOSE i where cmonth = '" + month + "'"; simpleButtonClose.Enabled = false; simpleButtonOpen.Enabled = true; } else { sql = "insert into COST_TEMP_CLOSE(cmonth,closed) values('" + month + "',1)"; simpleButtonClose.Enabled = false; simpleButtonOpen.Enabled = true; } conn.EditDatabase(sql); conn.Close(); }
public string PullData_begin_end(tenday begindate, tenday enddate) { ConnDB conn = new ConnDB(); string sql = "select t_800_80.fid,t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.WW_Max," + "t_800_80.TTT_Aver,t_800_80.TTT_Max ,t_800_80.Num_of_Tmax ,t_800_80.TTT_Min,t_800_80.Sum_of_RRR ,t_800_80.TxTxTxAver," + "t_800_80.txtxtx_max,t_800_80.num_of_tmin,t_800_80.Num_of_RRR ,t_800_80.Num_of_Tx_Max,t_800_80.TxTxTx_Min,station2.lat,station2.lon " + "from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex where t_800_80.year = " + begindate.year + " and t_800_80.month = " + begindate.month + " and t_800_80.num_of_month =" + begindate.num_of_month + " or t_800_80.year = " + enddate.year + " and t_800_80.month =" + enddate.month + " " + " and t_800_80.num_of_month =" + enddate.num_of_month + " "; NpgsqlCommand cmd = conn.RunCmdPG(sql); NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); DataSet data = new DataSet(); da.Fill(data); conn.ClosePG(); string JsonString = string.Empty; JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0]))); return(JsonString); }
private void simpleButton复制_Click(object sender, EventArgs e) { MessageBoxButtons messButton = MessageBoxButtons.OKCancel; DialogResult dr = MessageBox.Show("将按最近的月度数据自动新增当前选择月份的数据,确认复制吗?", "复制最近月份数据", messButton); if (dr == DialogResult.OK) { ConnDB conn = new ConnDB(); string strsql; bool isok = false; DateTime dt1 = Convert.ToDateTime(dlpiform.dateTimePicker1.Text); DateTime dt2 = System.DateTime.Now; int month = (dt2.Year - dt1.Year) * 12 + (dt2.Month - dt1.Month); if (!dlpiform.Exist()) { if (month < -1) { MessageBox.Show("月份错误!"); } else { strsql = "insert into cost_direct_labour_price(yyyymm,work_type,price) select '" + dlpiform.dateTimePicker1.Text + "',work_type,price from cost_direct_labour_price where YYYYMM = (select Max(yyyymm) from cost_direct_labour_price) "; isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("复制成功!"); DirectLabourPriceQuery.RefreshEX(); this.Close(); } } } conn.Close(); } //Exist(); //ShowDetail(); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string sql = "update cost_linetype set cname = '" + textEditName.Text.ToString().Trim() + "',cname_mes = '" + textEditNameMes.Text.ToString().Trim() + "', saletype_id =" + comboBoxSaleType.SelectedValue.ToString() + ",WORK_SHOP =" + comboBoxWorkShop.SelectedValue.ToString(); sql = sql + " where cid = " + textEditID.Text.ToString(); string sql2 = "select cname from cost_linetype where cname = '" + textEditName.Text.ToString().Trim() + "' and cname_mes = '" + textEditName.Text.ToString().Trim() + "' and saletype_id = " + comboBoxSaleType.SelectedValue.ToString(); if ((textEditName.Text.ToString().Trim() != "" || textEditNameMes.Text.ToString().Trim() != "") && comboBoxSaleType.SelectedValue.ToString() != "0" && comboBoxWorkShop.SelectedValue.ToString() != "0") { int rows = conn.ReturnRecordCount(sql2); if (rows > 0) { MessageBox.Show("该线体已经存在!"); } else { bool isok = conn.EditDatabase(sql); if (isok) { MessageBox.Show("修改成功!"); LineTypeQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("不能为空值!"); } conn.Close(); }
/// <summary> /// 系统成本计算 /// </summary> private void CalculateSys(string month) { ConnDB conn = new ConnDB(); //string month = DateTime.Now.ToString("yyyy-MM"); string sql; int i, rows; bool isok; isok = CheckData5(month); if (isok) { sql = "select * from COST_MONTH_CALCULATE where cmonth ='" + month + "' and sale_type_id =15"; IDataParameter[] parameters = new IDataParameter[] { new SqlParameter("@cmonth", month), new SqlParameter("@saletypeid", "15") }; rows = conn.ReturnRecordCount(sql); if (rows > 0) { string sql1, sql2, sql3, sql4; sql1 = "delete from COST_DAY_CALCULATE where cdate like '" + month + "%' and sale_type_id =15"; sql2 = "delete from COST_MONTH_CALCULATE where cmonth ='" + month + "' and sale_type_id =15"; sql3 = "delete from COST_DEPT_CALCULATE where cdate like '" + month + "%' and sale_type_id =15"; sql4 = "delete from COST_DEPT_MONTH_CALCULATE where cmonth ='" + month + "' and sale_type_id =15"; conn.EditDatabase(sql1); conn.EditDatabase(sql2); conn.EditDatabase(sql3); conn.EditDatabase(sql4); conn.RunProcedure("costing", parameters, out i); } else { conn.RunProcedure("costing", parameters, out i); } } conn.Close(); }
private bool Import() { ConnDB conn = new ConnDB(); string strsql; string month = dateTimePickerMonth.Text; int rows, i; bool success = true; strsql = "select cdate from cost_product_quantity where cdate like '" + dateTimePickerMonth.Text + "%' and type = 1"; IDataParameter[] parameters = new IDataParameter[] { new SqlParameter("@cmonth", month), new SqlParameter("@type", "1") }; rows = conn.ReturnRecordCount(strsql); if (rows > 0) { MessageBox.Show("该月终端产出台数已经存在,要重新导入,请先清空该月数据!"); } else { try { conn.RunProcedure("COST_PRODUCT_IMPORT", parameters, out i); } catch { MessageBox.Show("失败!"); success = false; } if (success) { MessageBox.Show("导入成功!"); //showDetail(); } } conn.Close(); return(success); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into COST_QUANTITY_RATE(yyyy,quarter_id,rate) values('" + dateTimePicker1.Text.ToString() + "'," + comboBoxQuarter.SelectedValue.ToString() + ",ltrim(rtrim(" + Common.IsNull(textEditRate.Text.ToString()) + ")))"; strsql2 = "select * from COST_QUANTITY_RATE where yyyy ='" + dateTimePicker1.Text.ToString() + "' and quarter_id = " + Common.IsZero(comboBoxQuarter.SelectedValue.ToString()); if (comboBoxQuarter.SelectedValue.ToString() == "0") { MessageBox.Show("请选择季度"); } else if (textEditRate.Text.ToString() == "") { MessageBox.Show("终端台数比率不能为空!"); } else { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该比率已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); QuantityRateQuery.RefreshEX(); this.Close(); } } } conn.Close(); }
/// <summary> /// 导入考勤 /// </summary> private void ImportAttendance(string month) { ConnDB conn = new ConnDB(); string strsql, sqldel; int rows, i; //string month = DateTime.Now.ToString("yyyy-MM"); //string month = "2017-10"; strsql = "select * from COST_DIRECT_LABOUR_ATTENDANCE where cdate like '%" + month + "%'"; sqldel = "delete from COST_DIRECT_LABOUR_ATTENDANCE where cdate like '%" + month + "%'"; IDataParameter[] parameters = new IDataParameter[] { new SqlParameter("@cmonth", month) }; rows = conn.ReturnRecordCount(strsql); if (rows > 0) { conn.EditDatabase(sqldel); conn.RunProcedure("COST_ATTENDANCE_IMPORT", parameters, out i); } else { conn.RunProcedure("COST_ATTENDANCE_IMPORT", parameters, out i); } conn.Close(); }
private bool Import() { ConnDB conn = new ConnDB(); string strsql; string month = dateTimePickerMonth.Text; int rows, i; bool success = true; strsql = "select * from COST_DIRECT_LABOUR_ATTENDANCE where cdate like '%" + dateTimePickerMonth.Text + "%'"; IDataParameter[] parameters = new IDataParameter[] { new SqlParameter("@cmonth", month) }; rows = conn.ReturnRecordCount(strsql); if (rows > 0) { MessageBox.Show("该月考勤数据已经存在,要重新导入,请先清空该月数据!"); } else { try { conn.RunProcedure("COST_ATTENDANCE_IMPORT", parameters, out i); } catch { MessageBox.Show("失败!"); success = false; } if (success) { MessageBox.Show("导入成功!"); //showDetail(); } } conn.Close(); return(success); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into cost_Position(CNAME,person_type_id) values(LTRIM(rtrim('" + textEditName.Text.ToString() + "'))," + comboBoxPersonType.SelectedValue.ToString() + ")"; strsql2 = "select cname from cost_Position where cname = LTRIM(rtrim('" + textEditName.Text.ToString() + "'))"; if (textEditName.Text.ToString().Trim() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该职位已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); PositionQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("名称不能为空!"); } conn.Close(); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into cost_dept(CNAME,SALETYPE_ID) values('" + textEditName.Text.ToString().Trim() + "'," + comboBoxSaleType.SelectedValue.ToString() + ")"; strsql2 = "select cname from cost_dept where cname = '" + textEditName.Text.ToString().Trim() + "'"; if (textEditName.Text.ToString().Trim() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该部门已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); DeptQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("名称不能为空!"); } conn.Close(); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into COST_TEMP_EMPLOYEE_PRICE(TYPE,SUPPLIER,BEGIN_DATE,END_DATE,FROM_TYPE,PRICE,MEAL_PRICE,NIGHT_PRICE,TRAVEL_PRICE,REGULAR_PRICE) values(1,'" + textEditSupplier.Text.ToString().Trim() + "','" + dateTimePickerBegin.Text.ToString() + "','" + dateTimePickerEnd.Text.ToString() + "','" + comboBoxType.SelectedValue.ToString() + "','" + Common.IsNull(textEditPrice.Text.ToString().Trim()) + "','" + Common.IsNull(textEditMeal.Text.ToString().Trim()) + "','" + Common.IsNull(textEditNight.Text.ToString().Trim()) + "','" + Common.IsNull(textEditTravel.Text.ToString().Trim()) + "','" + Common.IsNull(textEditRegular.Text.ToString().Trim()) + "')"; strsql2 = "select price from COST_TEMP_EMPLOYEE_PRICE where SUPPLIER ='" + textEditSupplier.Text.ToString().Trim() + "' and not (begin_date > '" + dateTimePickerEnd.Text.ToString() + "' or end_date <'" + dateTimePickerBegin.Text.ToString() + "') and from_type = '" + comboBoxType.SelectedValue.ToString() + "' and type = 1"; if (textEditSupplier.Text.ToString().Trim() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("此时间范围的价格信息已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); TempEmpPriceQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("供应商不能为空!"); } conn.Close(); }
public BindingList <PlDetailData> GetDataSource() { BindingList <PlDetailData> list = new BindingList <PlDetailData>(); string sql = $"SELECT d.PONO,d.PARTNO,b.PARTNAME,b.LOTNO,d.CTNSN RUNNINGNO,'' RVMANAGINGNO,'' SHELVE,d.ISSUINGKEY,d.* FROM TXP_ISSPACKDETAIL d\n" + $"INNER JOIN TXP_ISSTRANSBODY b ON d.ISSUINGKEY = b.ISSUINGKEY AND d.PONO = b.PONO AND d.PARTNO = b.PARTNO\n" + $"INNER JOIN TXP_ISSPALLET l ON d.ISSUINGKEY = l.ISSUINGKEY AND d.SHIPPLNO = l.PALLETNO\n" + $"WHERE l.PALLETNO = '{plno.PlNo}' AND l.ISSUINGKEY = '{plno.RefNo}'"; if (plno.PlOut != "") { sql = $"SELECT c.PONO,c.PARTNO,p.PARTNAME,c.LOTNO,c.RUNNINGNO,c.RVMANAGINGNO,c.SHELVE,l.ISSUINGKEY FROM TXP_CARTONDETAILS c \n" + $"INNER JOIN TXP_PART p ON c.PARTNO = p.PARTNO\n" + "LEFT JOIN TXP_ISSPALLET l ON c.PLOUTNO = l.PLOUTNO\n" + $"WHERE c.PLOUTNO = '{plno.PlOut}' \n" + "GROUP BY c.PONO,c.PARTNO,p.PARTNAME,c.LOTNO,c.RUNNINGNO,c.RVMANAGINGNO,c.SHELVE,l.ISSUINGKEY\n" + $"ORDER BY c.PONO,p.PARTNAME,c.LOTNO,c.RUNNINGNO"; } Console.WriteLine(sql); DataSet dr = new ConnDB().GetFill(sql); foreach (DataRow r in dr.Tables[0].Rows) { list.Add(new PlDetailData() { Id = list.Count + 1, OrderNo = r["pono"].ToString(), PartNo = r["partno"].ToString(), PartName = r["partname"].ToString(), LotNo = r["lotno"].ToString(), SerialNo = r["runningno"].ToString(), Shelve = r["shelve"].ToString(), RefInv = r["issuingkey"].ToString(), }); } return(list); }
public static bool ModifyRight() { ConnDB conn = new ConnDB(); string sql; bool right = false; sql = "select m.permission from COST_USER i left join COST_USER_ROLE r on i.CID = r.USER_ID and r.HAVE_RIGHT = 'true' left join COST_ROLE_PERMISSION p on r.ROLE_ID = p.ROLE_ID and p.HAVE_RIGHT = 'true' left join COST_MODULE_PERMISSION m on p.PERMISSION_ID = m.CID where i.CNAME = '" + Logon.GetCname() + "' and m.module_name = '临时工基本信息'"; DataSet ds = conn.ReturnDataSet(sql); for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { if (ds.Tables[0].Rows[j][0].ToString() == "修改") { right = true; break; } else { right = false; } } conn.Close(); return(right); }
private void simpleButtonExport_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string cno, dept1, dept2, dept3, dept, status, days, cmonth; days = "days"; //DateTime begin_date, end_date; DataSet ds = new DataSet(); status = comboBoxStatus.SelectedIndex.ToString(); cno = textEditNo.Text.ToString().Trim(); cmonth = dateTimePickerMonth.Text.ToString(); //begin_date = Convert.ToDateTime(dateTimePickerBegin.Text.ToString()); //end_date = Convert.ToDateTime(dateTimePickerEnd.Text.ToString()); dept1 = comboBoxDept1.SelectedValue.ToString(); dept2 = comboBoxDept2.SelectedValue.ToString(); dept3 = comboBoxDept3.SelectedValue.ToString(); dept = comboBoxDept.SelectedValue.ToString(); IDataParameter[] parameters = new IDataParameter[] { new SqlParameter("@month", cmonth), new SqlParameter("@cno", cno), new SqlParameter("@dept1", dept1), new SqlParameter("@dept2", dept2), new SqlParameter("@dept3", dept3), new SqlParameter("@dept", dept) }; try { ds = conn.RunProcedure("COST_TEMP_EMPLOYEE_DAYS", parameters, days); } catch { MessageBox.Show("失败!"); } bool isok = Common.DataSetToExcel(ds, true); if (isok) { MessageBox.Show("导出完成!"); } }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into cost_standard_point(yyyymm,sale_type_id,standard_point) values('" + dateTimePicker1.Text.ToString() + "'," + comboBoxSaleType.SelectedValue + ",ltrim(rtrim(" + textEditStandardPoint.Text.ToString() + ")))"; strsql2 = "select yyyymm from cost_standard_point where yyyymm ='" + dateTimePicker1.Text.ToString() + "' and sale_type_id = " + comboBoxSaleType.SelectedValue; if (textEditStandardPoint.Text.ToString() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该单点标准成本已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); StandardPointQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("单点标准成本不能为空!"); } conn.Close(); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into cost_dept_map(dept_id,DEPT1,DEPT2,DEPT3,DEPT4) values(" + comboBoxDept.SelectedValue.ToString() + ",'制造中心','" + textEdit2nd.Text.ToString().Trim() + "','" + textEdit3rd.Text.ToString().Trim() + "','" + textEdit4th.Text.ToString().Trim() + "')"; strsql2 = "select * from cost_dept_map where isnull(DEPT2,'') = '" + textEdit2nd.Text.ToString().Trim() + "' and isnull(DEPT3,'') = '" + textEdit3rd.Text.ToString().Trim() + "' and isnull(DEPT4,'') = '" + textEdit4th.Text.ToString().Trim() + "'"; if (textEdit4th.Text.ToString().Trim() != "" && comboBoxDept.SelectedValue.ToString() != "0") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该考勤部门已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); DeptMapQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("不能为空!"); } conn.Close(); }
private void simpleButtonSubmit_Click(object sender, EventArgs e) { if (ischange == true) { ConnDB conn = new ConnDB(); string strsql; int cid = RoleQuery.GetCid(); string sql = "insert into COST_ROLE_PERMISSION select i.cid,p.cid,'false' from COST_ROLE i cross join COST_MODULE_PERMISSION p left join COST_ROLE_PERMISSION r on i.CID = r.ROLE_ID and p.CID = r.PERMISSION_ID where i.cid = " + cid + " and r.role_id is null"; conn.EditDatabase(sql); bool isok = false; //gridView1.FocusInvalidRow(); for (int i = 0; i < gridView1.RowCount; i++) { strsql = "update i set i.have_right = '" + gridView1.GetDataRow(i).ItemArray[4].ToString() + "' from COST_ROLE_PERMISSION i where i.ROLE_ID = " + gridView1.GetDataRow(i).ItemArray[0].ToString() + " and i.PERMISSION_ID = " + gridView1.GetDataRow(i).ItemArray[1].ToString(); isok = conn.EditDatabase(strsql); } if (isok) { MessageBox.Show("提交成功!"); showDetail(); this.Close(); } else { MessageBox.Show("失败!"); } conn.Close(); ischange = false; } else { MessageBox.Show("没有可更新的数据!"); } }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into cost_depreciation(yyyymm,sale_type_id,depreciation) values('" + dateTimePicker1.Text.ToString() + "'," + comboBoxSaleType.SelectedValue.ToString() + ",ltrim(rtrim(" + Common.IsNull(textEditDepreciation.Text.ToString()) + ")))"; strsql2 = "select yyyymm from cost_depreciation where yyyymm ='" + dateTimePicker1.Text.ToString() + "' and sale_type_id = " + comboBoxSaleType.SelectedValue.ToString(); if (textEditDepreciation.Text.ToString() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该折旧费已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); DepreciationQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("折旧费不能为空!"); } conn.Close(); }
private void TempEmpInsert_Load(object sender, EventArgs e) { DateTime dt = DateTime.Now; DateTime startMonth = dt.AddDays(1 - dt.Day); //本月月初 this.dateTimePickerMonth.Value = startMonth; dateTimePickerMonth.Focus(); SendKeys.Send("{RIGHT} "); ConnDB conn = new ConnDB(); string sql; string month = dateTimePickerMonth.Text.ToString(); sql = "select closed from COST_TEMP_CLOSE where cmonth ='" + month + "'"; DataSet ds = conn.ReturnDataSet(sql); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0][0].ToString() == "1") { simpleButtonClose.Enabled = false; simpleButtonOpen.Enabled = true; } else { simpleButtonClose.Enabled = true; simpleButtonOpen.Enabled = false; } } else { simpleButtonClose.Enabled = true; simpleButtonOpen.Enabled = false; } conn.Close(); }
public string only_pull_snow(tenday startdate, tenday enddate) { ConnDB conn = new ConnDB(); string sql = " "; sql = "select t_800_93.*,station2.lat,station2.lon from t_800_93 inner join station2 on t_800_93.sindex = station2.sindex " + " where t_800_93.year between " + startdate.year + " and " + enddate.year + " and t_800_93.month between " + startdate.month + " and " + enddate.month + " " + "and t_800_93.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " "; NpgsqlCommand cmd = conn.RunCmdPG(sql); NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); DataSet data = new DataSet(); da.Fill(data); conn.ClosePG(); string JsonString = string.Empty; JsonString = JsonConvert.SerializeObject(SnowDirectionTableToList(data.Tables[0])); return(JsonString); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into cost_direct_labour(CNO,CNAME,DEPT_ID,POSITION_ID,LINETYPE_ID,PERSON_TYPE_ID,FORBIDDEN) values(LTRIM(rtrim('" + textEditNo.Text.ToString() + "')),LTRIM(rtrim('" + textEditName.Text.ToString() + "'))," + comboBoxDept.SelectedValue.ToString() + "," + comboBoxPosition.SelectedValue.ToString() + "," + Common.IsZero(comboBoxLineType.SelectedValue.ToString()) + ",3,0)"; strsql2 = "select cname from cost_direct_labour where cno = LTRIM(rtrim('" + textEditNo.Text.ToString().Trim() + "'))"; if (textEditNo.Text.ToString().Trim() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该人员已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); DirectLabourQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("工号不能为空!"); } conn.Close(); }
private void simpleButtonOK_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string strsql, strsql2; int rows; strsql = "insert into COST_TEMP_WORKER_PRICE(yyyymm,sale_type_id,price) values('" + dateTimePicker1.Text.ToString() + "'," + comboBoxSaleType.SelectedValue + "," + textEditTempWorkerPrice.Text.ToString().Trim() + ")"; strsql2 = "select yyyymm from COST_TEMP_WORKER_PRICE where yyyymm ='" + dateTimePicker1.Text.ToString() + "' and sale_type_id = " + comboBoxSaleType.SelectedValue; if (textEditTempWorkerPrice.Text.ToString() != "") { rows = conn.ReturnRecordCount(strsql2); if (rows > 0) { MessageBox.Show("该临时工费率已经存在!"); } else { bool isok = conn.EditDatabase(strsql); if (isok) { MessageBox.Show("添加成功!"); TempWorkerPriceQuery.RefreshEX(); this.Close(); } else { MessageBox.Show("失败!"); } } } else { MessageBox.Show("费率不能为空!"); } conn.Close(); }
private void simpleButton导入_Click(object sender, EventArgs e) { simpleButton导入.Enabled = false; ConnDB conn = new ConnDB(); string file = textEditFile.Text.ToString().Trim(); string sheet = comboBoxSheet.Text.ToString(); if (file == "") { MessageBox.Show("没有选择文件", "提示信息", MessageBoxButtons.OK); simpleButton导入.Enabled = true; } else if (sheet == "") { MessageBox.Show("请选择EXCEL表", "提示信息", MessageBoxButtons.OK); simpleButton导入.Enabled = true; } else { Clear(); MethodCaller mc = new MethodCaller(Import); IAsyncResult result = mc.BeginInvoke(file, sheet, AsyncShowDetail, mc); } }
private void simpleButton导出到excel_Click(object sender, EventArgs e) { ConnDB conn = new ConnDB(); string matl, cmonth; matl = "matl"; DataSet ds; cmonth = dateTimePickerMonth.Text.ToString(); IDataParameter[] parameters = new IDataParameter[] { new SqlParameter("@cmonth", cmonth) }; try { ds = conn.RunProcedure("COST_MATL_QUERY_LINE", parameters, matl); bool isok = DataSetToExcel(ds, true); if (isok) { MessageBox.Show("导出完成!"); } } catch { MessageBox.Show("失败!"); } }
public List <BookingInvoicePallet> GetInvoicePlBooked(string issuekey) { string sql = $"SELECT * FROM TBT_BOOKINGLIST WHERE ISSUINGKEY = '{issuekey}' AND BOOKED > 3"; List <BookingInvoicePallet> obj = new List <BookingInvoicePallet>(); Console.WriteLine(sql); DataSet dr = new ConnDB().GetFill(sql); foreach (DataRow r in dr.Tables[0].Rows) { obj.Add(new BookingInvoicePallet() { id = obj.Count + 1, issuekey = r["issuingkey"].ToString(), pltype = r["pltype"].ToString(), plno = r["palletno"].ToString(), pltotal = int.Parse(r["pltotal"].ToString()), ploutno = r["ploutno"].ToString(), plstatus = int.Parse(r["ploutsts"].ToString()), custname = r["custname"].ToString(), }); } return(obj); }
private void BindChart() { ConnDB conn = new ConnDB(); string strsql, strsql2, month; int deptid; deptid = (int)comboBoxDept.SelectedValue; List <Series> myseries = new List <Series>(); month = dateTimePicker1.Text.ToString(); if (deptid == 0) { strsql = "select SALE_TYPE_NAME 营业类型,dept_name 部门,Cdate 日期,DIRECT_HOURS 直接人工小时数,cast(round(DIRECT_COST,2) as decimal(18,2)) 直接人工成本,INDIRECT_HOURS 间接人工小时数,cast(round(INDIRECT_COST,2) as decimal(18,2)) 间接人工成本,cast(round(POINTCOUNT,2) as decimal(18,2)) 点数,cast(round(COST,2) as decimal(18,2)) 预估成本,COST_POINT 预估单点成本,STANDARD_POINT 标准单点成本,cast(round(isnull(STANDARD_COST,0),2) as decimal(18,2)) 标准成本,cast(round(PROFIT,2) as decimal(18,2)) 盈亏 from COST_DEPT_CALCULATE where cdate like '" + month + "%' and sale_type_id =2"; strsql2 = "select distinct dept_name 部门 from COST_DEPT_CALCULATE where cdate like '" + month + "%' and sale_type_id =2"; } else { strsql = "select SALE_TYPE_NAME 营业类型,dept_name 部门,Cdate 日期,DIRECT_HOURS 直接人工小时数,cast(round(DIRECT_COST,2) as decimal(18,2)) 直接人工成本,INDIRECT_HOURS 间接人工小时数,cast(round(INDIRECT_COST,2) as decimal(18,2)) 间接人工成本,cast(round(POINTCOUNT,2) as decimal(18,2)) 点数,cast(round(COST,2) as decimal(18,2)) 预估成本,COST_POINT 预估单点成本,STANDARD_POINT 标准单点成本,cast(round(isnull(STANDARD_COST,0),2) as decimal(18,2)) 标准成本,cast(round(PROFIT,2) as decimal(18,2)) 盈亏 from COST_DEPT_CALCULATE where cdate like '" + month + "%' and sale_type_id =2 and dept_id =" + deptid; strsql2 = "select distinct dept_name 部门 from COST_DEPT_CALCULATE where cdate like '" + month + "%' and sale_type_id =2 and dept_id = " + deptid; } DataSet ds = conn.ReturnDataSet(strsql); DataTable dt = ds.Tables[0]; DataSet ds2 = conn.ReturnDataSet(strsql2); XYDiagram dg = (XYDiagram)chartControlDeptDay.Diagram; if (dg != null) { dg.Panes.Clear(); } chartControlDeptDay.Series.Clear(); chartControlDeptDay.Annotations.Clear(); chartControlDeptDay.DataSource = dt; for (int i = 0; i < ds2.Tables[0].Rows.Count; i++) { //预估成本 myseries.Add(new Series(ds2.Tables[0].Rows[i][0].ToString() + "预估成本", DevExpress.XtraCharts.ViewType.Spline)); myseries[i * 2].ArgumentDataMember = dt.Columns["日期"].ToString(); myseries[i * 2].ValueDataMembersSerializable = dt.Columns["预估成本"].ToString(); chartControlDeptDay.Series.Add(myseries[i * 2]); //标准成本 myseries.Add(new Series(ds2.Tables[0].Rows[i][0].ToString() + "标准成本", DevExpress.XtraCharts.ViewType.Spline)); myseries[i * 2 + 1].ArgumentDataMember = dt.Columns["日期"].ToString(); myseries[i * 2 + 1].ValueDataMembersSerializable = dt.Columns["标准成本"].ToString(); chartControlDeptDay.Series.Add(myseries[i * 2 + 1]); //设置过滤 DataFilter df = new DataFilter("部门", "System.String", DataFilterCondition.Equal, ds2.Tables[0].Rows[i][0].ToString()); //series1.DataFiltersConjunctionMode = ConjunctionTypes.Or; myseries[i * 2].DataFilters.Clear(); myseries[i * 2].DataFilters.AddRange(new DataFilter[] { df }); myseries[i * 2 + 1].DataFilters.Clear(); myseries[i * 2 + 1].DataFilters.AddRange(new DataFilter[] { df }); //设置pane XYDiagram diagram = (XYDiagram)chartControlDeptDay.Diagram; if (i > 0) { //// Add secondary axes to the diagram, and adjust their options. //diagram.SecondaryAxesX.Add(new SecondaryAxisX(Convert.ToString(i - 1))); //diagram.SecondaryAxesY.Add(new SecondaryAxisY(Convert.ToString(i - 1))); //diagram.SecondaryAxesX[i - 1].Alignment = AxisAlignment.Near; //diagram.SecondaryAxesY[i - 1].Alignment = AxisAlignment.Near; //diagram.Panes[ds2.Tables[0].Rows[i][0].ToString()] diagram.Panes.Add(new XYDiagramPane(ds2.Tables[0].Rows[i][0].ToString())); SplineSeriesView myView1 = (SplineSeriesView)myseries[i * 2].View; //myView1.AxisX = diagram.SecondaryAxesX[i - 1]; //myView1.AxisY = diagram.SecondaryAxesY[i - 1]; myView1.Pane = diagram.Panes[i - 1]; SplineSeriesView myView2 = (SplineSeriesView)myseries[i * 2 + 1].View; //myView2.AxisX = diagram.SecondaryAxesX[i - 1]; //myView2.AxisY = diagram.SecondaryAxesY[i - 1]; myView2.Pane = diagram.Panes[i - 1]; chartControlDeptDay.Annotations.AddTextAnnotation(Convert.ToString(i)); XYDiagramPaneBase myPane = diagram.Panes[i - 1]; ((FreePosition)chartControlDeptDay.Annotations[i].ShapePosition).DockTarget = diagram.Panes[i - 1]; ((FreePosition)chartControlDeptDay.Annotations[i].ShapePosition).DockCorner = DockCorner.LeftTop; TextAnnotation myTextAnnotation = (TextAnnotation)chartControlDeptDay.AnnotationRepository.GetElementByName(Convert.ToString(i)); //myTextAnnotation.Text = "<color=red>" + ds2.Tables[0].Rows[i][0].ToString() + " </color>"; myTextAnnotation.Text = ds2.Tables[0].Rows[i][0].ToString(); myTextAnnotation.ShapeKind = ShapeKind.Rectangle; myTextAnnotation.ConnectorStyle = AnnotationConnectorStyle.None; } else { chartControlDeptDay.Annotations.AddTextAnnotation("Annotation 0"); XYDiagramPaneBase myPane = diagram.DefaultPane; ((FreePosition)chartControlDeptDay.Annotations[i].ShapePosition).DockTarget = myPane; ((FreePosition)chartControlDeptDay.Annotations[i].ShapePosition).DockCorner = DockCorner.LeftTop; TextAnnotation myTextAnnotation = (TextAnnotation)chartControlDeptDay.AnnotationRepository.GetElementByName("Annotation 0"); //myTextAnnotation.Text = "<color=red>" + ds2.Tables[0].Rows[i][0].ToString() + " </color>"; myTextAnnotation.Text = ds2.Tables[0].Rows[i][0].ToString(); myTextAnnotation.ShapeKind = ShapeKind.Rectangle; myTextAnnotation.ConnectorStyle = AnnotationConnectorStyle.None; } ((SplineSeriesView)myseries[i * 2].View).MarkerVisibility = DefaultBoolean.True; ((SplineSeriesView)myseries[i * 2 + 1].View).MarkerVisibility = DefaultBoolean.True; ((SplineSeriesView)myseries[i * 2].View).Color = Color.Orange; ((SplineSeriesView)myseries[i * 2 + 1].View).Color = Color.OliveDrab; ((FreePosition)chartControlDeptDay.Annotations[i].ShapePosition).InnerIndents.All = 0; ((FreePosition)chartControlDeptDay.Annotations[i].ShapePosition).OuterIndents.Top = 10; diagram.PaneDistance = 10; diagram.PaneLayoutDirection = PaneLayoutDirection.Vertical; diagram.DefaultPane.SizeMode = PaneSizeMode.UseWeight; diagram.DefaultPane.Weight = 1.2; } //外观 chartControlDeptDay.Legend.Visibility = DefaultBoolean.True; chartControlDeptDay.Legend.AlignmentHorizontal = LegendAlignmentHorizontal.Center; chartControlDeptDay.Legend.AlignmentVertical = LegendAlignmentVertical.TopOutside; chartControlDeptDay.Legend.Direction = LegendDirection.LeftToRight; conn.Close(); }