public static bool CheckExists(JieTuo entity)
{
string sql = string.Empty;
sql = "select * from jietuo where tph='" +
entity.Tph + "' and sn='" + entity.Sn + "' and iswhole=" +
entity.IsWhole + " and scaner='" + entity.Scaner + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
/*
* create table jietuo(id integer primary key autoincrement,
* tph varchar(20),sn varchar(20),iswhole integer,
* scaner varchar(30),scantime datetime);
*/
public static void Save(JieTuo entity)
{
string sql = string.Empty;
sql = "insert into jietuo(tph,sn,iswhole,scaner,scantime) values('" +
entity.Tph + "','" + entity.Sn + "'," + entity.IsWhole + ",'" +
entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
//MessageBox.Show(sql);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
private void btn_UpdataBaseData_Click(object sender, EventArgs e)
{
DataSet ds = new DB().GetFactoryAndStorage();
if (ds == null || ds.Tables.Count < 2)
{
MessageBox.Show("获取数据失败，请检查网络！");
return;
}
StringBuilder sql = new StringBuilder();
sql.Append("delete from mydicts;");
sql.Append("insert into mydicts(dicttype,dictvalue,dicttext) ");
foreach (DataRow dr in ds.Tables["Factory"].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}','{1}','{2}' union all ", 1, dr["id"], dr["name"]);
}
foreach (DataRow dr in ds.Tables["Storage"].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}','{1}','{2}' union all ", 2, dr["id"], dr["name"]);
}
if (sql.Length == 0)
{
return;
}
sql.Length = sql.Length - "union all ".Length;
if (SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql.ToString().Split(";".ToCharArray())) == 0)
{
MessageBox.Show("同步基础数据失败，请检查网络！");
return;
}
ds = new DB().GetLoc();
sql.Length = 0;
sql.Append("delete from kuweiinfo;");
sql.Append("insert into kuweiinfo(kuweicode) ");
foreach (DataRow dr in ds.Tables[0].Rows)
{
sql.AppendFormat(System.Globalization.CultureInfo.CurrentCulture, "select '{0}' union all ", dr["loc"]);
}
if (sql.Length == 0)
{
return;
}
sql.Length = sql.Length - "union all ".Length;
if (SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql.ToString().Split(";".ToCharArray())) == 0)
{
MessageBox.Show("同步基础数据失败，请检查网络！");
return;
}
MessageBox.Show("同步基础数据成功！");
}
public static void Delete(SendRecord entity)
{
string sql = string.Empty;
sql = "delete from sendrecord where so='" +
entity.So + "' and otherso='" + entity.OtherSo + "' and qufen='" +
entity.QuFen + "' and pnno='" +
entity.PnNo + "' and cpqufen='" +
entity.CpQuFen + "' and carno='" +
entity.CarNo + "' and status=''"
+ " and scaner='" + entity.Scaner + "'";
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
/*
* create table sendrecord(id integer primary key autoincrement,
* so varchar(20),otherso varchar(200),qufen varchar(20),
* pnno varchar(20),cpqufen varchar(20),sl integer,
* carno varchar(30),status integer,scaner varchar(30),
* scantime datetime);
*/
public static void Save(SendRecord entity)
{
string sql = string.Empty;
sql = "insert into sendrecord(so,otherso,qufen,pnno,cpqufen,carno,status,scaner,scantime) values('" +
entity.So + "','" + entity.OtherSo + "','" +
entity.QuFen + "','" + entity.PnNo + "','" +
entity.CpQuFen + "','" + entity.CarNo + "'," + entity.Status + ",'" +
entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
//MessageBox.Show(sql);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sql);
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 1)
{
string sql = string.Format(@"select receiveno AS 收货单,pnno AS 产品,tno AS 托盘,cuno AS SN from receivedetail where receiveno='{0}' and scaner='{1}'", asnMain.Receiveno, Program.UserID);
this.dg_Resume.DataSource = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
new DB().SetDataGridCloumnWidth(dg_Resume);
}
else if (tabControl1.SelectedIndex == 2)
{
string sql = string.Format(@"select receiveno AS 收货单,pnno AS 产品,sl AS 数量 from receiverecord where receiveno='{0}'", asnMain.Receiveno);
this.dg_Summarizing.DataSource = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
new DB().SetDataGridCloumnWidth(dg_Summarizing);
}
}
public static bool CheckExists(SendRecord entity)
{
string sql = string.Empty;
sql = "select * from sendrecord where so='" +
entity.So + "' and otherso='" + entity.OtherSo + "' and qufen='" +
entity.QuFen + "' and pnno='" +
entity.PnNo + "' and cpqufen='" +
entity.CpQuFen + "' and carno='" +
entity.CarNo + "' and status=" + entity.Status + ""
+ " and scaner='" + entity.Scaner + "'";
//MessageBox.Show(sql);
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
return(dt != null && dt.Rows.Count == 1);
}
private void tabControl1_SelectedIndexChanged_1(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 1)
{
string sql = string.Format(@"select sn as SN,tph as 托盘,kuwei as 库位 from pandiandetail where scaner='{0}'", Program.UserID);
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
this.dg_Resume.DataSource = dt;
new DB().SetDataGridCloumnWidth(dg_Resume);
}
if (tabControl1.SelectedIndex == 2)
{
string sql = string.Format(@"select pnno as 产品 ,sl as 数量 from pandianrecord");
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql);
this.dg_Summarizing.DataSource = dt;
new DB().SetDataGridCloumnWidth(dg_Summarizing);
}
}
public static DataTable GetUserData(string uid)
{
string sql = "select tph as 托盘号,sn as SN,xxjh as 下乡机号 from zutuo where scaner='" + uid + "'";
return(SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql));
}
public static DataTable GetUserData(string uid)
{
string sql = "select tph as 托盘号,mdkw as 目的库位 from antuoyiku where scaner='" + uid + "'";
return(SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql));
}
public static DataTable GetUserData(string uid)
{
string sql = "select ykw as 原库位,cp as 产品,sl as 数量,mdkw as 目的库位 from anhuoyiku where scaner='" + uid + "'";
return(SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql));
}
public string SaveAsnDetail(string RollBack, string scanType, ASNMain asnmain, ASNDetail asndetail)
{
if (RollBack == string.Empty)
{
try
{
string sqlForExist = "select * from receivedetail where cuno='" + asndetail.cuno + "'";
DataTable dtExist = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sqlForExist);
if (dtExist.Rows.Count > 0)
{
return("此产品已存在，请不要重复扫描！");
}
else
{
if (scanType == "SN")
{
string[] sql = new string[2];
string sqlForSelectMain = string.Format(@"select * from receiverecord where pnno='{0}'", this.pnno);
DataTable dtMain = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sqlForSelectMain);
string sqlInsertDetail = string.Format(@"insert into receivedetail (pnno,tno,hold,xxjh,cuno,scanner,scantime,status) values ('{0}','{1}','{2}',
'{3}','{4}','{5}',{6})", asndetail.pnno, asndetail.tno, asndetail.hold, asndetail.xxjh, asndetail.cuno, asndetail.scanner, asndetail.scantime,
asndetail.status);
sql[1] = sqlInsertDetail;
if (dtMain.Rows.Count == 0)
{
string sqlInsertMain = string.Format(@"insert into receiverecord (receiveno,factory,factoryid,storage,storageid,carno,receivetype,qufen,pnno,
tno,hold,sl,scaner,scantime,status) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}','{13}',{14})",
asnmain.Receiveno, asnmain.Factory, asnmain.Factoryid, asnmain.Storage, asnmain.Storageid, asnmain.Carno, asnmain.Receivetype,
asnmain.Qufen, asnmain.Pnno, asnmain.Tno, asnmain.Hold, asnmain.Sl, asnmain.Scanner, asnmain.Scantime, asnmain.Status);
sql[0] = sqlInsertMain;
}
else
{
string sqlUpdateMain = string.Format(@"update receiverecord set sl=sl+{0} where pnno='{1}'", asnmain.Sl, asnmain.Pnno);
sql[0] = sqlUpdateMain;
}
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
}
else
{
string sqlInsertDetail = string.Format(@"insert into receivedetail (pnno,tno,hold,xxjh,cuno,scanner,scantime,status) values ('{0}','{1}','{2}',
'{3}','{4}','{5}',{6})", asndetail.pnno, asndetail.tno, asndetail.hold, asndetail.xxjh, asndetail.cuno, asndetail.scanner, asndetail.scantime,
asndetail.status);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlInsertDetail);
}
}
return(string.Empty);
}
catch (Exception ex)
{
return(ex.ToString());
}
}
else
{
string sqlForSelect = string.Empty;
string sqlForDelete = string.Empty;
if (scanType == "SN")
{
sqlForSelect = string.Format(@"select * from receivedetail where cuno='{0}'", asndetail.cuno);
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sqlForSelect);
if (dt.Rows.Count == 0)
{
return("产品尚未扫描，不能撤销！");
}
else
{
if (scanType == "SN")
{
string[] sql = new string[2];
sqlForDelete = string.Format("delete from receivedetail where cuno='{0}'", asndetail.cuno);
string sqlForUpdateMain = string.Format(@"update receiverecord set sl=sl-{0} where pnno='{1}'", asnmain.Sl, asnmain.Pnno);
sql[0] = sqlForDelete;
sql[1] = sqlForUpdateMain;
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
//sdo.ExecuteNonQuery(sqlForDelete);
}
else
{
sqlForDelete = string.Format("delete from receivedetail where cuno='{0}'", asndetail.cuno);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlForDelete);
}
return("产品撤销扫描成功！");
}
}
else
{
sqlForSelect = string.Format(@"select * from receivedetail where tno='{0}'");
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sqlForSelect);
if (dt.Rows.Count == 0)
{
return("托盘尚未扫描，不能撤销！");
}
else
{
sqlForDelete = string.Format("delete from receivedetail where tno='{0}'", asndetail.cuno);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlForDelete);
return("托盘撤销扫描成功！");
}
}
}
}
{ /*
* create table senddetail(so varchar(20),
* sn varchar(20),fahuotype integer,
* xxjh varchar(20),tph varchar(20),
* scaner varchar(30),scantime datetime,status integer);
*/
/*
* create table sendrecord(id integer primary key autoincrement,
* so varchar(20),otherso varchar(200),qufen varchar(20),
* pnno varchar(20),cpqufen varchar(20),sl integer,
* carno varchar(30),status integer,scaner varchar(30),
* scantime datetime);
*/
public static void Save(SendDetail entity, SendRecord parent)
{
string pnno = string.Empty;
if (entity.Sn != null && entity.Sn.Length > 12)
{
pnno = entity.Sn.Substring(2, 10);
}
string sel = "select * from sendrecord where so='" + parent.So + "' and pnno='" + pnno + "'";
DataTable dt = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sel);
string[] sql = new string[2];
sql[1] = "insert into senddetail(so,sn,fahuotype,xxjh,tph,scaner,scantime) values('" +
entity.So + "','" + entity.Sn + "'," +
entity.FahuoType + ",'" +
entity.Xxjh + "','" +
entity.Tph + "','" +
entity.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
/*
* sql = "select * from sendrecord where so='" +
* entity.So + "' and otherso='" + entity.OtherSo + "' and qufen='" +
* entity.QuFen + "' and pnno='" +
* entity.PnNo + "' and cpqufen='" +
* entity.CpQuFen + "' and carno='" +
* entity.CarNo + "' and status=" + entity.Status + ""
+ " and scaner='" + entity.Scaner + "'";
* */
if (pnno.Length > 0)
{
if (dt == null || dt.Rows.Count == 0)
{
SendRecord sendrecord = new SendRecord();
sendrecord.So = parent.So;
sendrecord.OtherSo = parent.OtherSo;
sendrecord.CarNo = parent.CarNo;
sendrecord.QuFen = parent.QuFen;
sendrecord.Status = 0;
sendrecord.CpQuFen = string.Empty;
sendrecord.PnNo = pnno;
sendrecord.Sl = 1;
sendrecord.Scaner = Program.UserID;
sendrecord.date = System.DateTime.Now;
sql[0] = "insert into sendrecord(sl,so,otherso,qufen,pnno,cpqufen,carno,status,scaner,scantime) values(1,'" +
sendrecord.So + "','" + sendrecord.OtherSo + "','" +
sendrecord.QuFen + "','" + sendrecord.PnNo + "','" +
sendrecord.CpQuFen + "','" + sendrecord.CarNo + "'," + sendrecord.Status + ",'" +
sendrecord.Scaner + "','" +
System.DateTime.Now.ToString("s")
+ "')";
//SendRecordManager.Save(sendrecord);
}
else
{
sql[0] = "update sendrecord set sl=sl+1 where so='" + parent.So + "' and pnno='" + pnno + "'";
}
}
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
}
public static DataTable GetUserData(string so, string uid)
{
string sql = "select sn as SN,xxjh as 下乡机号,tph as 托盘号 from senddetail where so='" + so + "' and scaner='" + uid + "'";
return(SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sql));
}
public string SaveCheckData(CheckMain ck, CheckDetailRecord cdr, string ScanType, string RollBack)
{
//string sqlInsert=string.Format(@"insert into ")
try
{
if (RollBack == string.Empty)
{
string selectForExist = string.Empty;
if (ScanType == "SN")
{
selectForExist = "select * from pandiandetail where sn='" + this.sn + "'";
}
else
{
selectForExist = "select * from pandiandetail where tph='" + this.tph + "'";
}
DataTable dtExist = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(selectForExist);
if (dtExist.Rows.Count > 0)
{
return("扫描纪录已存在，请不要重复扫描！");
}
else
{
if (ScanType == "SN")
{
string[] sql = new string[2];
string sqlInsertDetail = string.Format(@"insert into pandiandetail(sn,xxjh,tph,scaner,scantime,kuwei) values ('{0}','{1}','{2}','{3}',
'{4}','{5}')", cdr.Sn, cdr.Xxjh, cdr.Tph, cdr.Scaner, cdr.Scantime, cdr.Kw);
sql[0] = sqlInsertDetail;
string sqlForSelectMain = "select * from pandianrecord where pnno='" + ck.Pnno + "'";
DataTable dtMain = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(sqlForSelectMain);
if (dtMain.Rows.Count == 0)
{
string sqlInsertMain = string.Format(@"insert into pandianrecord (kw,pnno,cpqufen,sl,status) values ('{0}','{1}','{2}',{3},{4})",
ck.Kw, ck.Pnno, ck.Cpqufen, ck.Sl, ck.Status);
sql[1] = sqlInsertMain;
}
else
{
string sqlUpdateMain = string.Format(@"update pandianrecord set sl=sl+{0} where pnno='{1}'", ck.Sl, ck.Pnno);
}
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
}
else
{
string sqlInsertDetail = string.Format(@"insert into pandiandetail(sn,xxjh,tph,scaner,scantime,kuwei) values ('{0}','{1}','{2}','{3}',
'{4}','{5}')", cdr.Sn, cdr.Xxjh, cdr.Tph, cdr.Scaner, cdr.Scantime, cdr.Kw);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlInsertDetail);
}
}
return(string.Empty);
}
else
{
string[] sql = new string[2];
string selectForExist = string.Empty;
if (ScanType == "SN")
{
selectForExist = "select * from pandiandetail where sn='" + this.sn + "'";
}
else
{
selectForExist = "select * from pandiandetail where tph='" + this.tph + "'";
}
DataTable dtExist = SqliteDbFactory.GetSqliteDbOperator().SelectFromSql(selectForExist);
if (dtExist.Rows.Count == 0)
{
return("此纪录尚未扫描，不能撤销扫描！");
}
else
{
string sqlForDelete = string.Empty;
if (ScanType == "SN")
{
sqlForDelete = string.Format(@"delete from pandiandetail where sn='{0}'", cdr.Sn);
string sqlMainRollBack = string.Format(@"update pandianrecord set sl=sl-{0} where pnno='{1}'", ck.Sl, ck.Pnno);
sql[0] = sqlForDelete;
sql[1] = sqlMainRollBack;
SqliteDbFactory.GetSqliteDbOperator().BatchExecute(sql);
}
else
{
sqlForDelete = string.Format(@"delete from pandiandetail where tph='{0}'", cdr.Tph);
SqliteDbFactory.GetSqliteDbOperator().ExecuteNonQuery(sqlForDelete);
}
return("撤销扫描成功！");
}
}
}
catch (Exception ex)
{
return(ex.ToString());
}
}
public static DataTable Count(string so)
{
return(SqliteDbFactory.GetSqliteDbOperator().SelectFromSql("select so as 单号, pnno as 产品号,sl as 数量 from sendrecord where so='" + so + "'"));
}
