private int addPurchasing(Data data)
{
string sqlStr = "";
int arraySize = data.A_Pc_parameter.Length;
for (int i = 0; i < arraySize; i++)
{
data.Pc_parameter = data.A_Pc_parameter[i];
data.Pc_purpose = data.A_Pc_purpose[i];
data.Pc_quantity = data.A_Pc_quantity[i];
data.Pc_typeId = data.A_Pc_typeId[i];
data.Pc_unitPrice = data.A_Pc_unitPrice[i];
data.Pc_remarks = data.A_Pc_remarks[i];
if (data.Pc_Method.IndexOf("入库") > -1)
{
sqlStr += addInventory(data);
}
if (data.Pc_Method.IndexOf("入账") > -1)
{
sqlStr += addFinancial(data);
}
sqlStr += getPurchasing(data);
}
DbSql ds = new DbSql();
//将数据添加进采购管理数据表中
if (!ds.ExecSql(sqlStr))
{
return(0);
}
return(2);
}
private void Form1_Load(object sender, EventArgs e)
{
#region Подключение к БД
var dataBase = new DbSql();
#endregion
#region Зона тестирования
//listBox1.Items.Add(); // Вывод в формочку
// Основные команды
// dataBase.AddTable();
// dataBase.DelTable();
// dataBase.AddColumnInt();
// dataBase.DelColumn();
// dataBase.AddDependence();
// dataBase.DelDependence();
ConsoleOut(dataBase); // Выводит иерархию в формочку
#endregion
#region Завершение связи с БД
dataBase.Close();
#endregion
}
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 insertDB(DataTable dt)
{
try
{
int errorcount = 0; //记录错误信息条数
int insertcount = 0; //记录插入成功条数
DbSql sqldb = new DbSql();
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlStr = "insert into K_TaobaoOrder values (";
for (int j = 0; j < 45; j++)
{
sqlStr += "'" + dt.Rows[i][j].ToString().Replace("\'", "''") + "' ,";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1) + ")";
if (sqldb.ExecSql(sqlStr))
{
insertcount++;
}
else
{
errorcount++;
}
}
return("{ error:'',msg:'导入成功" + insertcount + "条数据导入成功！" + errorcount + "条数据部分信息为空没有导入！'}");
}
catch (Exception ex)
{
return("{ error:'导入失败，插入数据库失败，请重试或联系管理员'}");
}
}
/****************************************************************************************************************************/
private void JoinX()
{
Spacing(X);
foreach (var dp in DC.Parameters)
{
if (dp.Crud != CrudEnum.Join)
{
continue;
}
switch (dp.Action)
{
case ActionEnum.From:
/* 已处理 */
break;
case ActionEnum.InnerJoin:
case ActionEnum.LeftJoin:
var tbm = DC.XC.GetTableModel(dp.TbMType);
CRLF(X); Tab(X);
Action(dp.Action, X, DC); Spacing(X); DbSql.TableX(tbm.TbName, X); As(X); DbSql.TableXAlias(dp.TbAlias, X);
break;
case ActionEnum.On:
CRLF(X); Tab(X); Tab(X);
Action(dp.Action, X, DC); Spacing(X); DbSql.Column(dp.TbAlias, dp.TbCol, X); Compare(dp.Compare, X, DC); DbSql.Column(dp.TableAliasTwo, dp.ColumnTwo, X);
break;
}
}
}
private void SelectSpecialDateFormatCol(DicParam dic)
{
if (dic.Crud == CrudEnum.Join)
{
if (dic.Option == OptionEnum.Column)
{
Function(dic.Func, X, DC); LeftRoundBracket(X); DbSql.Column(dic.TbAlias, dic.TbCol, X); Comma(X); StringConst(dic.Format, X); RightRoundBracket(X);
}
else if (dic.Option == OptionEnum.ColumnAs)
{
Function(dic.Func, X, DC); LeftRoundBracket(X); DbSql.Column(dic.TbAlias, dic.TbCol, X); Comma(X); StringConst(dic.Format, X); RightRoundBracket(X);
As(X); DbSql.ColumnAlias(dic.TbColAlias, X);
}
}
else if (dic.Crud == CrudEnum.Query)
{
if (dic.Option == OptionEnum.Column)
{
Function(dic.Func, X, DC); LeftRoundBracket(X); DbSql.Column(string.Empty, dic.TbCol, X); Comma(X); StringConst(dic.Format, X); RightRoundBracket(X);
}
else if (dic.Option == OptionEnum.ColumnAs)
{
Function(dic.Func, X, DC); LeftRoundBracket(X); DbSql.Column(string.Empty, dic.TbCol, X); Comma(X); StringConst(dic.Format, X); RightRoundBracket(X);
As(X); DbSql.ColumnAlias(dic.TbColAlias, X);
}
}
}
private void SelectSpecialNoFuncCol(DicParam dic)
{
if (dic.Crud == CrudEnum.Join)
{
if (dic.Option == OptionEnum.Column)
{
DbSql.Column(dic.TbAlias, dic.TbCol, X);
}
else if (dic.Option == OptionEnum.ColumnAs)
{
DbSql.Column(dic.TbAlias, dic.TbCol, X); As(X); DbSql.ColumnAlias(dic.TbColAlias, X);
}
}
else if (dic.Crud == CrudEnum.Query)
{
if (dic.Option == OptionEnum.Column)
{
DbSql.Column(string.Empty, dic.TbCol, X);
}
else if (dic.Option == OptionEnum.ColumnAs)
{
DbSql.Column(string.Empty, dic.TbCol, X); As(X); DbSql.ColumnAlias(dic.TbColAlias, X);
}
}
}
internal protected void Where()
{
var cons = DC.Parameters.Where(it => IsWhere(it))?.ToList();
if (cons == null)
{
return;
}
var where = cons.FirstOrDefault(it => it.Action == ActionEnum.Where);
var and = cons.FirstOrDefault(it => it.Action == ActionEnum.And);
var or = cons.FirstOrDefault(it => it.Action == ActionEnum.Or);
if (where == null &&
(and != null || or != null))
{
var aId = and == null ? -1 : and.ID;
var oId = or == null ? -1 : or.ID;
var flag = aId < oId || oId == -1;
Spacing(X); DbSql.WhereTrueOrFalse(DC, flag, X); Spacing(X);
}
foreach (var db in cons)
{
CRLF(X); Action(db.Action, X, DC); Spacing(X);
if (db.Group == null)
{
MultiCondition(db);
}
else
{
LeftRoundBracket(X);
MultiCondition(db);
RightRoundBracket(X);
}
}
}
internal protected void Sum()
{
Spacing(X);
var col = DC.Parameters.FirstOrDefault(it => IsSelectColumnParam(it));
var sum = col.Columns.FirstOrDefault(it => it.Func == FuncEnum.Sum)
?? col.Columns.FirstOrDefault(it => it.Func == FuncEnum.SumNullable);
if (sum != null)
{
var tbAlias = sum.Crud == CrudEnum.Query
? string.Empty
: sum.Crud == CrudEnum.Join
? sum.TbAlias
: string.Empty;
Function(sum.Func, X, DC);
LeftRoundBracket(X);
if (sum.Func == FuncEnum.Sum)
{
DbSql.Column(tbAlias, sum.TbCol, X);
}
else if (sum.Func == FuncEnum.SumNullable)
{
DbSql.ColumnReplaceNullValueForSum(tbAlias, sum.TbCol, X);
}
RightRoundBracket(X);
}
}
internal protected void OrderBy()
{
var dic = DC.Parameters.FirstOrDefault(it => it.Action == ActionEnum.From);
var tbm = DC.XC.GetTableModel(dic != null ? dic.TbMType : DC.TbM1);
if (DC.Parameters.Any(it => it.Action == ActionEnum.OrderBy))
{
CRLF(X); X.Append("order by"); Spacing(X); OrderByParams();
}
else
{
if (!IsPaging(DC))
{
return;
}
var col = DbSql.GetIndex(tbm.TbCols);
if (col != null)
{
CRLF(X); X.Append("order by"); Spacing(X);
if (DC.Crud == CrudEnum.Join)
{
DbSql.Column(dic.TbAlias, col.ColumnName, X); Spacing(X); X.Append("desc");
}
else
{
DbSql.Column(string.Empty, col.ColumnName, X); Spacing(X); X.Append("desc");
}
}
}
}
protected void yes_Click(object sender, EventArgs e)
{
if (new_ps.Text != again_ps.Text)
{
Response.Write("<script language='javascript' type='text/javascript'> alert('修改失败，两次输入新密码不一致，请重新输入')</script>");
return;
}
string sqlStr = "select * from K_Employee where Emp_account='" + Session["yhm"] + "' and Emp_password='******' and Emp_company='" + Session["company"] + "'";
DbSql ds = new DbSql();
DataTable da = new DataTable();
da = ds.FillDt(sqlStr);
if (da.Rows.Count > 0)
{
sqlStr = "update K_Employee set Emp_password='******' where Emp_id='" + Session["Emp_id"] + "'";
if (ds.ExecSql(sqlStr))
{
Session["yhm"] = null;
Response.Write("<script language='javascript' type='text/javascript'> alert('修改成功，请重新登录页面');window.parent.location.href='../Login.aspx';</script>");
}
else
{
Response.Write("<script language='javascript' type='text/javascript'> alert('修改失败，请重试或联系系统管理员')</script>");
}
}
else
{
Response.Write("<script language='javascript' type='text/javascript'> alert('密码错误或用户名不存在')</script>");
}
}
//更新客户的信息
private string UpdateInfo(Data data)
{
string sqlStr =
" update K_CustomerInfo set Ci_sex='" + data.sex +
"',Ci_name='" + data.name +
"',Ci_email='" + data.email +
"',Ci_phone='" + data.phone +
"',Ci_city='" + data.city +
"',Ci_vipLevel='" + data.level +
"',Ci_vipState='" + data.state +
"',Ci_address='" + data.addr +
"',Ci_marks='" + data.marks +
"',Ci_rank='" + data.rank +
"',Ci_birth='" + data.birth +
"',Ci_tradeAmount='" + data.tradeAmount +
"',Ci_goodsNum='" + data.goodsNum +
"',Ci_tradeCloseNum='" + data.tradeClose +
"',Ci_isInfoNew='" + 1 +
"' where Ci_infoId='" + data.infoName + "'";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
return(GetOldCustomer()); //成功
}
return("0"); //失败
}
/****************************************************************************************************************************/
private void CompareProcess(DicParam db)
{
if (db.Compare == CompareXEnum.In ||
db.Compare == CompareXEnum.NotIn)
{
InProcess(db);
}
else if (db.Compare == CompareXEnum.Like ||
db.Compare == CompareXEnum.NotLike)
{
LikeProcess(db);
}
else
{
Spacing(X);
if (db.Crud == CrudEnum.Join)
{
DbSql.Column(db.TbAlias, db.TbCol, X);
}
else if (DC.IsSingleTableOption())
{
DbSql.Column(string.Empty, db.TbCol, X);
}
Compare(db.Compare, X, DC); DbSql.DbParam(db.Param, X);
}
}
/****************************************************************************************************************************/
private void LikeStrHandle(DicParam dic)
{
Spacing(X);
var name = dic.Param;
var value = dic.ParamInfo.Value.ToString();
if (!value.Contains("%") &&
!value.Contains("_"))
{
X.Append("concat");
LeftRoundBracket(X); StringConst(Percent.ToString(), X); Comma(X); DbSql.DbParam(name, X); Comma(X); StringConst(Percent.ToString(), X); RightRoundBracket(X);
}
else if ((value.Contains("%") || value.Contains("_")) &&
!value.Contains("/%") &&
!value.Contains("/_"))
{
DbSql.DbParam(name, X);
}
else if (value.Contains("/%") ||
value.Contains("/_"))
{
DbSql.DbParam(name, X); Spacing(X); Escape(X); Spacing(X); StringConst(EscapeChar.ToString(), X);
}
else
{
throw XConfig.EC.Exception(XConfig.EC._015, $"{dic.Action}-{dic.Option}-{value}");
}
}
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 initPageData(string company)
{
pageData dataInfo = new pageData();
string sqlStr = "select * from K_info_Company where Co_company='" + company + "' order by Co_abbreviation select Tp_id,Tp_name from k_ProductType where Tp_company='" + company + "' and Tp_type='零件' order by Tp_name";
DbSql ds = new DbSql();
DataSet dset = new DataSet();
dset = ds.FillDs(sqlStr);
return(JsonConvert.SerializeObject(dset, new DataSetConverter()));
}
private void updateProduct(string id)
{
string sqlStr = "UPDATE K_Product SET Pd_inventory=(SELECT SUM(SKU_inventory) FROM K_ProductSku WHERE SKU_pdId='" + id + "') WHERE Pd_id='" + id + "';";
sqlStr += "UPDATE K_Product SET Pd_SKU =(SELECT COUNT(*) FROM K_ProductSku WHERE SKU_pdId='" + id + "') WHERE Pd_id='" + id + "'";
sqlStr += "UPDATE K_Product SET Pd_price =(SELECT (SELECT convert(nvarchar(20),MIN(SKU_price )) FROM K_ProductSku WHERE SKU_pdId='" + id + "')+'-'+convert(nvarchar(20),MAX(SKU_price)) FROM K_ProductSku WHERE SKU_pdId='" + id + "') WHERE Pd_id='" + id + "'";
DbSql ds = new DbSql();
ds.ExecSql(sqlStr);
}
private string onJob(Data data)
{
string sqlStr = " insert into K_Employee (Emp_account,Emp_name,Emp_password,Emp_deptId,Emp_sex,Emp_birthday,Emp_idcard,Emp_address,Emp_nowAddress,Emp_phone,Emp_positionId,Emp_company) values ('" + data.Emp_account + "','" + data.Emp_name + "','123456','" + data.Emp_deptId + "','" + data.Emp_sex + "','" + data.Emp_birthday + "','" + data.Emp_idcard + "','" + data.Emp_address + "','" + data.Emp_nowAddress + "','" + data.Emp_phone + "','" + data.Emp_positionId + "','" + data.Emp_company + "')";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
return("2"); //成功
}
return("0"); //失败
}
private string onJob(Data data)
{
string sqlStr = " update K_onoffDutyData set " + getSqlStr(data) + " where O_checkDate='" + data.O_checkDate + "' and O_employeeId='" + data.Emp_id + "'";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
return("2"); //成功
}
return("0"); //失败
}
private string onJob(Data data)
{
string sqlStr = " update K_Employee set Emp_name='" + data.Emp_name + "',Emp_deptId='" + data.Emp_deptId + "',Emp_sex='" + data.Emp_sex + "',Emp_birthday='" + data.Emp_birthday + "',Emp_idcard='" + data.Emp_idcard + "',Emp_address='" + data.Emp_address + "',Emp_nowAddress='" + data.Emp_nowAddress + "',Emp_phone='" + data.Emp_phone + "',Emp_positionId='" + data.Emp_positionId + "' where Emp_id='" + data.Emp_id + "'";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
return("2"); //成功
}
return("0"); //失败
}
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 string addNew(Data data)
{
string sqlStr = " insert into K_Income (Ic_payDate,Ic_payer,Ic_money,Ic_content,Ic_remarks,Ic_company,Ic_editDate,Ic_state,Ic_operator) values ('" +
data.Ic_payDate + "','" + data.Ic_payer + "','" + data.Ic_money + "','" + data.Ic_content + "','" + data.Ic_remarks + "','" + data.Ic_company + "','" + data.Ic_editDate + "','" + data.Ic_state + "','" + data.Ic_operator + "')";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
return("2"); //成功
}
return("0"); //失败
}
public void TestSQLSaveForeign()
{
PageForeign page = new PageForeign();
DateTime date = new DateTime(2019, 1, 3);
String path = page.getFileName(date);
using (StreamReader sr = new StreamReader(@"C:\Temp\htmldwl" + path, Encoding.GetEncoding("utf-8")))
{
ArrayList stock_data = page.ParseHtml(sr.ReadToEnd(), date);
DbSql.StockData_SaveForeign(stock_data);
}
}
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 string setUrl(string data, string id)
{
string sqlStr = "Update K_Employee set Emp_indexUrl='" + data + "' where Emp_id='" + id + "'";
DbSql ds = new DbSql();
DataTable da = new DataTable();
if (ds.ExecSql(sqlStr))
{
return("1");
}
return("0");
}
private string addNew(Data data)
{
string sqlStr = " insert into K_Outlay (Ol_payDate,Ol_payee,Ol_money,Ol_content,Ol_remarks,Ol_company,Ol_editDate,Ol_state,Ol_operator) values ('" +
data.Ol_payDate + "','" + data.Ol_payee + "','" + data.Ol_money + "','" + data.Ol_content + "','" + data.Ol_remarks + "','" + data.Ol_company + "','" + data.Ol_editDate + "','" + data.Ol_state + "','" + data.Ol_operator + "')";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
return("2"); //成功
}
return("0"); //失败
}
private string setPermission(Data data, HttpContext context)
{
string sqlStr = " update K_Employee set Emp_permissions='" + data.permissions + "' where Emp_id='" + data.id + "'";
DbSql ds = new DbSql();
if (ds.ExecSql(sqlStr))
{
reloadPermission(context);
return("2"); //成功
}
return("0"); //失败
}
private int addComponent(Data data)
{
string sqlStr = "";
DbSql ds = new DbSql();
sqlStr = "INSERT INTO K_Component (Cp_typeId, Cp_manufacturerId, Cp_parameter, Cp_price, Cp_unit, Cp_inventory, Cp_remarks, Cp_pictrue,Cp_company) VALUES ('" +
data.Cp_typeId + "', '" + data.Cp_manufacturerId + "', '" + data.Cp_parameter + "', '" + Double.Parse(0 + data.Cp_price) + "','" + data.Cp_unit + "', '" + Double.Parse(0 + data.Cp_inventory) + "','" + data.Cp_remarks + "','" + data.Cp_pictrue + "','" + data.company + "')";
if (!ds.ExecSql(sqlStr))
{
return(0);
}
return(2);
}
private void IsNullProcess(DicParam db)
{
Spacing(X);
if (db.Crud == CrudEnum.Join)
{
DbSql.Column(db.TbAlias, db.TbCol, X);
}
else if (DC.IsSingleTableOption())
{
DbSql.Column(string.Empty, db.TbCol, X);
}
Spacing(X); Option(db.Option, X, DC);
}
private void LikeProcess(DicParam db)
{
Spacing(X);
if (db.Crud == CrudEnum.Join)
{
DbSql.Column(db.TbAlias, db.TbCol, X);
}
else if (DC.IsSingleTableOption())
{
DbSql.Column(string.Empty, db.TbCol, X);
}
Compare(db.Compare, X, DC); LikeStrHandle(db);
}
