private void FrmYQ005_Load(object sender, EventArgs e)
{
this.ClearQueryCondition();
DataTable db = new DataTable();
//医院编码
string sql = " select YYMC,YYBM from ysxt_YyBM order by YYBM ";
db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
comboBox1.DataSource = db;
comboBox1.DisplayMember = "YYMC";
comboBox1.ValueMember = "YYBM";
comboBox1.Text = "";
comboBox4.Text = "托管药库订单";
}
private void frmFilter2_Load(object sender, EventArgs e)
{
this.ClearQueryCondition();
DataTable db = new DataTable();
//医院编码
string sql = " select YYMC,YYBM from ysxt_YyBM ";
db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
comboBox1.DataSource = db;
comboBox1.DisplayMember = "YYMC";
comboBox1.ValueMember = "YYBM";
comboBox1.Text = "";
//for (int i = 0; i < db.Rows.Count; i++)
//{
// DataRow row = db.Rows[i];
// this.comboBox1.Items.Add(row["vt_name"].ToString() + "|" + row["vt_ID"].ToString());
//}
//药品剂型
sql = @" select ypjxmc,ypjxbm from ysxt_ypjx";
db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
comboBox2.DataSource = db;
comboBox2.DisplayMember = "ypjxmc";
comboBox2.ValueMember = "ypjxbm";
comboBox2.Text = "";
//采购模式
sql = @" select cgmc,cgbm from ysxt_cgms";
db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
comboBox3.DataSource = db;
comboBox3.DisplayMember = "cgmc";
comboBox3.ValueMember = "cgbm";
comboBox3.Text = "";
//订单处理状态
sql = @" select mc,bm from ysxt_ddclzt";
db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
comboBox7.DataSource = db;
comboBox7.DisplayMember = "mc";
comboBox7.ValueMember = "bm";
comboBox7.Text = "";
}
/// <summary>
/// 生成保存dgv的语句
/// </summary>
/// <param name="dgv"></param>
/// <param name="tbName">表名</param>
/// <param name="conn">连接</param>
/// <returns></returns>
public static string GetUpdateStr(DataGridView dgv, string tbName, SqlConnection conn)
{
string strPKName = dgv.Columns[0].Name;
string strPKCode = "";
//SqlCommand sqlcmd = null;
string strSql = "";
string strInsert = "insert into " + tbName + " (";
//生成插入语句前半部分
for (int i = 1; i < dgv.ColumnCount; i++)
{
if (Convert.ToString(dgv.Columns[i].DataPropertyName) == "")
{
continue;
}
strInsert += dgv.Columns[i].Name + ",";
}
strInsert = strInsert.Substring(0, strInsert.Length - 1);
strInsert = strInsert + ") values (";
for (int i = 0; i < dgv.Rows.Count; i++)
{
strPKCode = Convert.ToString(dgv.Rows[i].Cells[strPKName].Value);
//strPKCode = dgv.Rows[i].Cells[strPKName].Value == null ? null : dgv.Rows[i].Cells[strPKName].Value.ToString();
//sqlcmd = new SqlCommand(string.Format("select {0} from {1} where {2} = '{3}'", strPKName, tbName, strPKName, strPKCode), conn);
//object o = sqlcmd.ExecuteScalar();
//if (o != null && o != System.DBNull.Value && o.ToString() != "")//update
if (Convert.ToInt32(SqlAccess.ExecuteScalar("select " + strPKName + " from " + tbName + " where " + strPKName + " = N'" + strPKCode + "'", conn)) > 0)
{
strSql = strSql + string.Format("update {0} set ", tbName);
for (int j = 1; j < dgv.ColumnCount; j++)
{
if (Convert.ToString(dgv.Columns[j].DataPropertyName) == "")
{
continue;
}
strSql = strSql + dgv.Columns[j].Name + " = " + Public.SqlParm(dgv.Rows[i].Cells[j].Value) + ",";
}
strSql = strSql.Substring(0, strSql.Length - 1);
strSql = strSql + string.Format(" where {0} = N'{1}'\n", strPKName, strPKCode);
}
else//insert
{
strSql = strSql + strInsert;
for (int j = 1; j < dgv.ColumnCount; j++)
{
if (Convert.ToString(dgv.Columns[j].DataPropertyName) == "")
{
continue;
}
strSql = strSql + Public.SqlParm(dgv.Rows[i].Cells[j].Value) + ",";
}
strSql = strSql.Substring(0, strSql.Length - 1);
strSql = strSql + ")\n";
}
}
//判断一下删除的
DataTable dt = SqlAccess.ExecuteSqlDataTable("select " + strPKName + " from " + tbName, conn);
for (int i = 0; i < dt.Rows.Count; i++)
{
strPKCode = Convert.ToString(dt.Rows[i][strPKName]);
if (Public.FindDgvRow(dgv, new string[] { strPKName }, new string[] { strPKCode }, 0, dgv.Rows.Count) == -1)
{
strSql += "delete from " + tbName + " where " + strPKName + " = " + Public.SqlParm(strPKCode) + "\n";
}
}
return(strSql);
}
public static DataTable CheckDataTable(DataTable dt, string tableName, string cHeadKey, string cFlag)
{
// WriteLog.writeLog("校验开始" + DateTime.Now + "tableName" + tableName);
dt.Columns.Add("Flag");//标识
// string cValue = "";
DataTable dtcompare = null;
Hashtable htCompare = new Hashtable();
string cBodyKey = "";
try
{
dtcompare = SqlAccess.ExecuteSqlDataTable(" select distinct cdefine28 from " + tableName + " with(nolock) where cdefine28 like'" + cHeadKey + "%'", ufconnstr_111);
if (dtcompare != null && dtcompare.Rows.Count > 0)
{
foreach (DataRow dr in dtcompare.Rows)
{
htCompare.Add(dr[0].ToString(), "");
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["Flag"] = "0";
}
return(dt);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
//判断
cBodyKey = ClsSystem.gnvl(dt.Rows[i]["SERIALKEY"], "");
if (htCompare.ContainsKey(cHeadKey + dt.Rows[i]["SERIALKEY"]))
{
dt.Rows[i]["Flag"] = "1";
// dt.Rows[i]["cFlag"] = "1";
}
else
{
dt.Rows[i]["Flag"] = "0";
}
}
DataView rowfilter1 = new DataView(dt);
rowfilter1.RowFilter = "Flag = '" + cFlag + "'";
rowfilter1.RowStateFilter = DataViewRowState.CurrentRows;
DataTable dts = rowfilter1.ToTable();
return(dts);
}
catch (Exception ex)
{
MessageBox.Show("具体原因是：\n" + ex.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(dt);
}
finally
{
// WriteLog.writeLog("校验结束" + DateTime.Now + "tableName" + tableName);
}
// dt.Columns.Add("Flag");//标识
//// string cValue = "";
// DataTable dt = null;
// string cBodyKey = "";
// try
// {
// dt = new DataTable();
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// //判断
// if (i == 0)
// {
// }
// cBodyKey = ClsSystem.gnvl(dt.Rows[i]["SERIALKEY"], "");
// cValue = ClsSystem.gnvl(SqlAccess.ExecuteScalar(" select cdefine28 from " + tableName + " with(nolock) where cdefine28='" + cHeadKey + cBodyKey + "'", ufconnstr_111), "");
// // dt = ClsSystem.gnvl(SqlAccess.ExecuteSqlDataTable(" select cdefine28 from " + tableName + " with(nolock) where cdefine28 like'" + cHeadKey + "%'", ufconnstr_111), "");
// if (cValue != "")
// {
// dt.Rows[i]["Flag"] = "1";
// // dt.Rows[i]["cFlag"] = "1";
// }
// else
// {
// dt.Rows[i]["Flag"] = "0";
// }
// }
// DataView rowfilter1 = new DataView(dt);
// rowfilter1.RowFilter = "Flag = '" + cFlag + "'";
// rowfilter1.RowStateFilter = DataViewRowState.CurrentRows;
// DataTable dts = rowfilter1.ToTable();
// return dts;
// }
// catch (Exception ex)
// {
// MessageBox.Show("具体原因是：\n" + ex.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
// return dt;
// }
// finally
// {
// }
}
private void button6_Click(object sender, EventArgs e)
{
string sql = "";
try
{
sql = @"select inv.cinvcode,inv.cInvName,inv.cCurrencyName,inv.cEnglishName,ex.cidefine8,
inv.cInvStd,inv.cEnterprise ,ex.cidefine1,ex.cidefine2,inv.cFile ,ex.cidefine3,c.cComUnitName, ex.cidefine4
from Inventory inv join Inventory_extradefine ex on inv.cinvcode=ex.cInvCode
join ComputationUnit c on c.cComunitCode=inv.cCAComUnitCode
where isnull(inv.cInvDefine10,'')='' order by inv.cinvcode";
this.Cursor = Cursors.WaitCursor;
if (dgv2.Rows.Count > 0)
{
dgv2.Rows.Clear();
}
DataTable db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
if (db.Rows.Count > 0)
{
for (int i = 0; i < db.Rows.Count; i++)
{
dgv2.Rows.Add();
dgv2.Rows[i].Cells["cinvcode"].Value = ClsSystem.gnvl(db.Rows[i]["cinvcode"], "");
dgv2.Rows[i].Cells["cInvName"].Value = ClsSystem.gnvl(db.Rows[i]["cInvName"], "");
dgv2.Rows[i].Cells["cidefine1"].Value = ClsSystem.gnvl(db.Rows[i]["cidefine1"], "");
dgv2.Rows[i].Cells["cCurrencyName"].Value = ClsSystem.gnvl(db.Rows[i]["cCurrencyName"], "");
dgv2.Rows[i].Cells["cEnglishName"].Value = ClsSystem.gnvl(db.Rows[i]["cEnglishName"], "");
dgv2.Rows[i].Cells["cidefine8"].Value = ClsSystem.gnvl(db.Rows[i]["cidefine8"], "");
dgv2.Rows[i].Cells["cInvStd"].Value = ClsSystem.gnvl(db.Rows[i]["cInvStd"], "");
dgv2.Rows[i].Cells["cEnterprise"].Value = ClsSystem.gnvl(db.Rows[i]["cEnterprise"], "");
dgv2.Rows[i].Cells["cidefine2"].Value = ClsSystem.gnvl(db.Rows[i]["cidefine2"], "");
dgv2.Rows[i].Cells["cFile"].Value = ClsSystem.gnvl(db.Rows[i]["cFile"], "");
dgv2.Rows[i].Cells["cidefine3"].Value = ClsSystem.gnvl(db.Rows[i]["cidefine3"], "");
dgv2.Rows[i].Cells["cComUnitName"].Value = ClsSystem.gnvl(db.Rows[i]["cComUnitName"], "");
dgv2.Rows[i].Cells["cidefine4"].Value = ClsSystem.gnvl(db.Rows[i]["cidefine4"], "");
}
}
this.Cursor = Cursors.Default;
// MessageBox.Show("未上传的存货查询完成", "提示", MessageBoxButtons.OK);
}
catch (Exception ex)
{
this.Cursor = Cursors.Default;
MessageBox.Show(ex.Message.ToString(), "错误", MessageBoxButtons.OK);
throw;
}
}
private void button6_Click(object sender, EventArgs e)
{
string sql = "";
try
{
// sql = @"select inv.cinvcode,inv.cInvName,inv.cCurrencyName,inv.cEnglishName,ex.cidefine8,
// inv.cInvStd,inv.cEnterprise ,ex.cidefine1,ex.cidefine2,inv.cFile ,ex.cidefine3,c.cComUnitName, ex.cidefine4
// from Inventory inv join Inventory_extradefine ex on inv.cinvcode=ex.cInvCode
// join ComputationUnit c on c.cComunitCode=inv.cCAComUnitCode
// where isnull(inv.cInvDefine10,'')='' order by inv.cinvcode";
sql = @"select top 100 inv.cinvcode ,inv.cinvname, iex.cidefine1,com.cComUnitName , sum( case when isnull(bgspstop,0) =1 or isnull(bstopflag,0) =1 then 0 else ISNULL(iQuantity,0)-isnull(fStopQuantity,0) - ISNULL(fOutQuantity,0) end) AS fAvailQtty
from CurrentStock csk join inventory inv on inv.cinvcode=csk.cinvcode
left join Inventory_extradefine iex on iex.cinvcode=inv.cinvcode
left join ComputationUnit com on com.cComUnitCode=inv.cComUnitCode
where isnull(iex.cidefine1,'')<>''
group by inv.cinvcode ,inv.cinvname,iex.cidefine1 ,com.cComUnitName
having sum( case when isnull(bgspstop,0) =1 or isnull(bstopflag,0) =1 then 0 else ISNULL(iQuantity,0)-isnull(fStopQuantity,0) - ISNULL(fOutQuantity,0) end) <>0 ";
this.Cursor = Cursors.WaitCursor;
if (dgv2.Rows.Count > 0)
{
dgv2.Rows.Clear();
}
DataTable db = SqlAccess.ExecuteSqlDataTable(sql, Program.ConnectionString);
if (db.Rows.Count > 0)
{
for (int i = 0; i < db.Rows.Count; i++)
{
dgv2.Rows.Add();
dgv2.Rows[i].Cells["cinvcode"].Value = ClsSystem.gnvl(db.Rows[i]["cinvcode"], "");
dgv2.Rows[i].Cells["cInvName"].Value = ClsSystem.gnvl(db.Rows[i]["cInvName"], "");
dgv2.Rows[i].Cells["ZXSPBM"].Value = ClsSystem.gnvl(db.Rows[i]["cidefine1"], "");
dgv2.Rows[i].Cells["SPLX"].Value = "药品";
dgv2.Rows[i].Cells["KCSL"].Value = ClsSystem.gnvl(db.Rows[i]["fAvailQtty"], "");
dgv2.Rows[i].Cells["KCDW"].Value = ClsSystem.gnvl(db.Rows[i]["cComUnitName"], "");
}
}
this.Cursor = Cursors.Default;
// MessageBox.Show("未上传的存货查询完成", "提示", MessageBoxButtons.OK);
}
catch (Exception ex)
{
this.Cursor = Cursors.Default;
MessageBox.Show(ex.Message.ToString(), "错误", MessageBoxButtons.OK);
return;
}
}
private string Qury01(string cType)
{
string xmlData = "";
string ResultXml = "";
string czlx = "1";//新增
string strsql = @"sselect cInvCCode as SPLX ,cInvDefine1 as YPLX, cInvName as tym,'' as CPM,
'' as YWM, cInvName as SPM,cInvDefine2 as YPSPTXM, cInvDefine3 as YPJX,
cInvStd as GG,cEnterprise as SCQYMC,'' as CPLB,'' as YPBWM,cFile as YPPZWH,
'' as BZCZ,'' as BZDW,cPackingType as CGYYDW,'' as BZSL,'' as BZFS,'' as TZMS
from inventory where isnull(cInvDefine10,'')<>'' ";
DataTable db = SqlAccess.ExecuteSqlDataTable(strsql, conn);
if (db.Rows.Count > 0)
{
for (int i = 0; i < db.Rows.Count; i++)
{
xmlData = "";
ResultXml = "";
xmlData = @"<?xml version=""1.0"" encoding=""utf-8""?>";
xmlData = xmlData + "<XMLDATA>";
xmlData = xmlData + "<HEAD>";
xmlData = xmlData + "<IP>" + SendMessage.GetIP() + "</IP>";
xmlData = xmlData + "<MAC>" + SendMessage.GetMAC() + "</MAC>";
xmlData = xmlData + "<BZXX/>";
xmlData = xmlData + "</HEAD>";
xmlData = xmlData + "<MAIN>";
xmlData = xmlData + "<CZLX>" + czlx + "</CZLX>";
xmlData = xmlData + "<SPLX>" + ClsSystem.gnvl(db.Rows[i]["SPLX"], "") + "</SPLX>";
xmlData = xmlData + "<YPLX>" + ClsSystem.gnvl(db.Rows[i]["YPLX"], "") + "</YPLX>";
xmlData = xmlData + "<TYM>" + ClsSystem.gnvl(db.Rows[i]["TYM"], "") + "</TYM>";
xmlData = xmlData + "<CPM>" + ClsSystem.gnvl(db.Rows[i]["CPM"], "") + "</CPM>";
xmlData = xmlData + "<YWM>" + ClsSystem.gnvl(db.Rows[i]["YWM"], "") + "</YWM>";
xmlData = xmlData + "<SPM>" + ClsSystem.gnvl(db.Rows[i]["SPM"], "") + "</SPM>";
xmlData = xmlData + "<YPSPTXM>" + ClsSystem.gnvl(db.Rows[i]["YPSPTXM"], "") + "</YPSPTXM>";
xmlData = xmlData + "<YPJX>" + ClsSystem.gnvl(db.Rows[i]["YPJX"], "") + "</YPJX>";
xmlData = xmlData + "<GG>" + ClsSystem.gnvl(db.Rows[i]["GG"], "") + "</GG>";
xmlData = xmlData + "<SCQYMC>" + ClsSystem.gnvl(db.Rows[i]["SCQYMC"], "") + "</SCQYMC>";
xmlData = xmlData + "<CPLB>" + ClsSystem.gnvl(db.Rows[i]["CPLB"], "") + "</CPLB>";
xmlData = xmlData + "<YPBWM>" + ClsSystem.gnvl(db.Rows[i]["YPBWM"], "") + "</YPBWM>";
xmlData = xmlData + "<YPPZWH>" + ClsSystem.gnvl(db.Rows[i]["YPPZWH"], "") + "</YPPZWH>";
xmlData = xmlData + "<BZCZ>" + ClsSystem.gnvl(db.Rows[i]["BZCZ"], "") + "</BZCZ>";
xmlData = xmlData + "<BZDW>" + ClsSystem.gnvl(db.Rows[i]["BZDW"], "") + "</BZDW>";
xmlData = xmlData + "<CGYYDW>" + ClsSystem.gnvl(db.Rows[i]["CGYYDW"], "") + "</CGYYDW>";
xmlData = xmlData + "<BZSL>" + ClsSystem.gnvl(db.Rows[i]["BZSL"], "") + "</BZSL>";
xmlData = xmlData + "<BZFS>" + ClsSystem.gnvl(db.Rows[i]["BZFS"], "") + "</BZFS>";
xmlData = xmlData + "<TZMS>" + ClsSystem.gnvl(db.Rows[i]["TZMS"], "") + "</TZMS>";
xmlData = xmlData + "</MAIN>";
xmlData = xmlData + "</XMLDATA>";
DataSet ds = SendMessage.QuryData(cType, xmlData);
if (ds.Tables.Count > 0)
{
if (ClsSystem.gnvl(ds.Tables[0].Rows[0]["ZTCLJG"], "") == "00000")
{
xmlData = @"<?xml version=""1.0"" encoding=""utf-8""?>";
xmlData = xmlData + "<XMLDATA>";
xmlData = xmlData + "<HEAD>";
xmlData = xmlData + "<IP>" + SendMessage.GetIP() + "</IP>";
xmlData = xmlData + "<MAC>" + SendMessage.GetMAC() + "</MAC>";
xmlData = xmlData + "<BZXX/>";
xmlData = xmlData + "</HEAD>";
xmlData = xmlData + "<MAIN>";
xmlData = xmlData + "<CXLX>07</CXLX>";
xmlData = xmlData + "<CXBH>" + ClsSystem.gnvl(db.Rows[i]["YPBWM"], "") + "</CXBH>";
xmlData = xmlData + "</MAIN>";
xmlData = xmlData + "</XMLDATA>";
DataSet rds = SendMessage.QuryData("YQ015", xmlData);
if (rds.Tables.Count > 0)
{
if (ClsSystem.gnvl(rds.Tables[0].Rows[0]["ZTCLJG"], "") == "00000")
{
//strsql = "insert into YS_Interface_Records(cMaker,dDate,cType,cFlag,ExCode,errMsg) values("
// + "'',getdate(),'" + cType + "','成功','" + VouchNO + "','')";
// SqlAccess.ExecuteSql(strsql, conn);
}
}
}
}
}
}
return(null);
}