/// <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();
}
