private string getComData(Data data)
{
string dataSum = "select count(*) from K_Component where Cp_isValid='1' and Cp_company='" + data.company + "'";
string sqlStr = "select top 200 * from K_Component ,k_ProductType ,K_info_Company where K_Component.Cp_typeId=k_ProductType.Tp_id and K_Component.Cp_manufacturerId=K_info_Company.Co_id";
sqlStr += " and Cp_isValid='1' and K_Component.Cp_company='" + data.company + "' and k_ProductType.Tp_company='" + data.company + "' and K_info_Company.Co_company='" + data.company + "'";
//order=1 以类型进行筛选，order=2 以制造商进行筛选
if (data.order == "1")
{
dataSum += "and Cp_typeId='" + data.require + "'";
sqlStr += " and Cp_typeId='" + data.require + "' and Cp_id not in (" + "select top " + (data.starCom - 1).ToString() + " Cp_id from K_Component where Cp_isValid='1' and Cp_company='" + data.company + "' and Cp_typeId='" + data.require + "' order by Cp_manufacturerId ) order by Cp_manufacturerId ";
}
else if (data.order == "2")
{
dataSum += "and Cp_manufacturerId='" + data.require + "'";
sqlStr += " and Cp_manufacturerId='" + data.require + "' and Cp_id not in (" + "select top " + (data.starCom - 1).ToString() + " Cp_id from K_Component where Cp_company='" + data.company + "' and Cp_manufacturerId='" + data.require + "' order by Cp_typeId ) order by Cp_typeId ";
}
else
{
sqlStr += " and Cp_id not in (" + "select top " + (data.starCom - 1).ToString() + " Cp_id from K_Component where Cp_company='" + data.company + "' order by Cp_typeId ) order by Cp_typeId ";
}
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
dataSum = ds.FillDt(dataSum).Rows[0][0].ToString();
DataRow dr = da.NewRow();
dr["Cp_parameter"] = dataSum; //
da.Rows.Add(dr); //将这一行记录加这个表中
return(JsonConvert.SerializeObject(da, new DataTableConverter()));
}
private string getSkuData(string data)
{
string sqlStr = "select * from K_ProductSku where SKU_pdId='" + data + "' order by SKU_id";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
return(JsonConvert.SerializeObject(da, new DataTableConverter()));
}
private string getData(Data data)
{
string sqlStr = " select top 200 * from K_Employee where Emp_company='" + data.company + "' ";
sqlStr += getSqlStr(data);
sqlStr += " and Emp_id not in ( select top " + (data.starCom - 1) + " Emp_id from K_Employee where Emp_company='" + data.company + "' " + getSqlStr(data) + " order by Emp_deptId,Emp_positionId desc)";
sqlStr += " order by Emp_id desc";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
da.Rows.Add(da); //将这一行记录加这个表中
sqlStr = "select count (*) from K_Employee where Emp_company='" + data.company + "' " + getSqlStr(data);
string temp = ds.FillDt(sqlStr).Rows[0][0].ToString();
DataRow row = da.NewRow();
row["Emp_id"] = temp;
da.Rows.Add(row);
return(JsonConvert.SerializeObject(da, new DataTableConverter()));
}
private string getData(Data data)
{
string sqlStr = " select top 200 * from K_TaobaoOrder where 1=1";
sqlStr += getSqlStr(data);
sqlStr += " and 订单编号 not in ( select top " + (data.starCom - 1) + " 订单编号 from K_TaobaoOrder where 1=1 " + getSqlStr(data) + " order by 订单付款时间 desc)";
sqlStr += " order by 订单付款时间 desc";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
da.Rows.Add(da); //将这一行记录加这个表中
sqlStr = "select count (*) from K_TaobaoOrder where 1=1 " + getSqlStr(data);
string temp = ds.FillDt(sqlStr).Rows[0][0].ToString();
DataRow row = da.NewRow();
row["订单编号"] = temp;
da.Rows.Add(row);
return(JsonConvert.SerializeObject(da, new DataTableConverter()));
}
private string getData(Data data)
{
string sqlStr = " select top 200 * from K_Purchasing ,k_ProductType ,K_info_Company where K_Purchasing.Pc_typeId=k_ProductType.Tp_id and K_Purchasing.Pc_manufacturerId=K_info_Company.Co_id";
sqlStr += " and K_Purchasing.Pc_isValid='1' and K_Purchasing.Pc_company='" + data.Pc_company + "' and k_ProductType.Tp_company='" + data.Pc_company + "' and K_info_Company.Co_company='" + data.Pc_company + "' ";
sqlStr += getSqlStr(data);
sqlStr += " and Pc_id not in ( select top " + (data.starCom - 1) + " Pc_id from K_Purchasing where Pc_isValid='1' and Pc_company='" + data.Pc_company + "' " + getSqlStr(data) + " order by Pc_date desc,Pc_editDate desc)";
sqlStr += " order by Pc_date desc,Pc_editDate desc";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
da.Rows.Add(da); //将这一行记录加这个表中
sqlStr = "select count (*) from K_Purchasing where Pc_isValid='1' and Pc_company='" + data.Pc_company + "' " + getSqlStr(data);
string temp = ds.FillDt(sqlStr).Rows[0][0].ToString();
DataRow row = da.NewRow();
row["Pc_id"] = temp;
da.Rows.Add(row);
return(JsonConvert.SerializeObject(da, new DataTableConverter()));
}
private string getPermission(string data)
{
string sqlStr = " select Emp_permissions from K_Employee where Emp_id='" + data + "'";
DbSql ds = new DbSql();
DataTable da = ds.FillDt(sqlStr);
if (da.Rows.Count < 1)
{
return("0"); //失败
}
return(da.Rows[0][0].ToString()); //成功
}
private void reloadPermission(HttpContext context)
{
string sqlStr = "select * from K_Employee where Emp_id='" + context.Session["yhm"].ToString() + "'";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
if (da.Rows.Count > 0)
{
context.Session["permissions"] = da.Rows[0]["Emp_permissions"].ToString();
}
}
private pageData initPageData(HttpContext context)
{
pageData dataInfo = new pageData();
dataInfo.company = context.Session["company"].ToString();
string sqlStr = "select Co_id,Co_abbreviation from K_info_Company where Co_company='" + dataInfo.company + "' order by Co_abbreviation";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
for (int i = 0; i < da.Rows.Count; i++)
{
dataInfo.co += "<li><input type='checkbox' data-Co_id='" + da.Rows[i][0].ToString() + "'/>" + da.Rows[i][1].ToString() + "</li>";
}
sqlStr = "select Tp_id,Tp_name from k_ProductType where Tp_company='" + dataInfo.company + "' and Tp_type='成品' order by Tp_name";
da = ds.FillDt(sqlStr);
for (int i = 0; i < da.Rows.Count; i++)
{
dataInfo.pd += "<li><input type='checkbox' data-Tp_id='" + da.Rows[i][0].ToString() + "'/>" + da.Rows[i][1].ToString() + "</li>";
}
return(dataInfo);
}
private string getAttData(Data data)
{
DateTime dt = Convert.ToDateTime(data.year + "-" + data.month + "-01");
DateTime dt2 = dt.AddMonths(1);
string sqlStr = "select * from K_onoffDutyData where O_employeeId='" + HttpContext.Current.Session["yhm"] + "' and O_checkDate>='" + dt + "' and O_checkDate<'" + dt2 + "' order by O_checkDate desc";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
da.Columns.Add("workTime", typeof(long));
da.Columns.Add("exTime", typeof(long));
for (int i = 0; i < da.Rows.Count; i++)
{
long temp = 0;
if (da.Rows[i]["O_onDuty1"].ToString() != "" && da.Rows[i]["O_offDuty1"].ToString() != "")
{
TimeSpan ts = DateTime.Parse(da.Rows[i]["O_offDuty1"].ToString()) - DateTime.Parse(da.Rows[i]["O_onDuty1"].ToString());
temp += (long)ts.TotalMinutes;
}
if (da.Rows[i]["O_onDuty2"].ToString() != "" && da.Rows[i]["O_offDuty2"].ToString() != "")
{
TimeSpan ts = DateTime.Parse(da.Rows[i]["O_offDuty2"].ToString()) - DateTime.Parse(da.Rows[i]["O_onDuty2"].ToString());
temp += (long)ts.TotalMinutes;
}
da.Rows[i]["workTime"] = temp;
temp = 0;
if (da.Rows[i]["O_onDuty3"].ToString() != "" && da.Rows[i]["O_offDuty3"].ToString() != "")
{
TimeSpan ts = DateTime.Parse(da.Rows[i]["O_offDuty3"].ToString()) - DateTime.Parse(da.Rows[i]["O_onDuty3"].ToString());
temp += (long)ts.TotalMinutes;
}
da.Rows[i]["exTime"] = temp;
}
long allTime = 0;
long exTime = 0;
long lostTime = 0;
for (int i = 0; i < da.Rows.Count; i++)
{
allTime += (long)da.Rows[i]["workTime"] + (long)da.Rows[i]["exTime"];
if ((long)da.Rows[i]["workTime"] < 570 && DateTime.Now > DateTime.Parse(da.Rows[i]["O_checkDate"].ToString()).AddHours(19.01))
{
lostTime += 570 - (long)da.Rows[i]["workTime"];
}
exTime += (long)da.Rows[i]["exTime"];
}
da.Rows.Add();
da.Rows[da.Rows.Count - 1][0] = allTime + "@" + lostTime + "@" + exTime + "";
return(JsonConvert.SerializeObject(da, new DataTableConverter()));
}
protected void yes_Click(object sender, EventArgs e)
{
string sqlStr = "SELECT * FROM k_ProductType WHERE Tp_company='" + Session["company"] + "' and Tp_name='" + ComTp.Text + "'";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
if (da.Rows.Count > 0)
{
Response.Write("<script language='javascript' type='text/javascript'> alert('添加失败，该类型已存在')</script>");
return;
}
sqlStr = "INSERT INTO k_ProductType (Tp_name, Tp_type,Tp_company) VALUES ('" + ComTp.Text + "', '零件','" + Session["company"] + "')";
if (!ds.ExecSql(sqlStr))
{
Response.Write("<script language='javascript' type='text/javascript'> alert('添加失败，请重试或者检查网络联系管理员')</script>");
}
workOut();
}
private int addComponent(Data data)
{
//返回1代表该SKU已存在，返回2代表添加成功，返回0代表数据库添加失败
string sqlStr = "SELECT * FROM K_ProductSku WHERE SKU_id='" + data.SKU_id + "'";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
if (da.Rows.Count > 0)
{
return(1);
}
sqlStr = "INSERT INTO K_ProductSku (SKU_inventory, SKU_pdId, SKU_parameter, SKU_name, SKU_id, SKU_remarks, SKU_price) VALUES ('" +
data.SKU_inventory + "', '" + data.SKU_pdId + "', '" + data.SKU_parameter + "', '" + data.SKU_name + "', '" + data.SKU_id + "', '" + data.SKU_remarks + "', '" + data.SKU_price + "')";
if (!ds.ExecSql(sqlStr))
{
return(0);
}
updateProduct(data.SKU_pdId);
return(2);
}
private string addInventory(Data data)
{
DbSql ds = new DbSql();
//检查该条数据是否已存在
string sqlStr = "select * from K_component where Cp_company='" + data.company + "' and Cp_manufacturerId='" + data.Pc_manufacturerId + "' and Cp_typeId='" + data.Pc_typeId + "' and Cp_parameter='" + data.Pc_parameter + "'";
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
//如果改数据已存在则添加库存，如果不存在新增零件并填写数据
if (da.Rows.Count > 0)
{
double price = (double.Parse(data.Pc_unitPrice) * double.Parse(data.Pc_quantity) + double.Parse(da.Rows[0]["Cp_price"].ToString()) * double.Parse(da.Rows[0]["Cp_inventory"].ToString())) /
(double.Parse(data.Pc_quantity) + double.Parse(da.Rows[0]["Cp_inventory"].ToString()));
sqlStr = "UPDATE K_Component SET Cp_inventory='" + (double.Parse(data.Pc_quantity) + double.Parse(da.Rows[0]["Cp_inventory"].ToString())).ToString("0.00") + "',Cp_price='" + price.ToString("0.00") + "' ";
sqlStr += "where Cp_id='" + da.Rows[0]["Cp_id"].ToString() + "' ";
}
else
{
sqlStr = "INSERT INTO K_Component (Cp_typeId, Cp_manufacturerId, Cp_parameter, Cp_price, Cp_inventory, Cp_remarks,Cp_company) VALUES ('"
+ data.Pc_typeId + "','" + data.Pc_manufacturerId + "','" + data.Pc_parameter + "','" + data.Pc_unitPrice + "','" + data.Pc_quantity + "','" + data.Pc_remarks + "','" + data.company + "')";
}
return(sqlStr);
}
private void loginSystem(string url)
{
string sqlStr = "select * from K_Employee where Emp_account='" + yhm.Text + "' and Emp_password='******'";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
if (da.Rows.Count > 0)
{
Response.Write(da.Rows.Count.ToString());
Session["yhm"] = yhm.Text;
Session["company"] = da.Rows[0]["Emp_company"].ToString();
Session["xm"] = da.Rows[0]["Emp_name"].ToString();
Session["indexUrl"] = da.Rows[0]["Emp_indexUrl"].ToString();
Session["permissions"] = da.Rows[0]["Emp_permissions"].ToString();
Session["Emp_id"] = da.Rows[0]["Emp_id"].ToString();
Response.Redirect(url);
}
else
{
Response.Write("<script language='javascript' type='text/javascript'> alert('密码错误或用户名不存在')</script>");
}
}