public static void GetPrevBillD(DataSet ds, string sTableMain, string sMainSql, string sTableDetail, string sDetailSql, string sWhereSql, string sKey1, string keyMember1, string sKey2, string keyMember2)
{
string sql = "";
if (ConvertUtil.GetDBString(sWhereSql) == "")
{
sql = "SELECT TOP 1 " + GetTableKeyColumn(sKey1, sKey2) + " FROM " + sTableMain + " WHERE " + sKey1 + " < " + keyMember1 + GetTableKey2Where(sTableMain, sKey2, keyMember2) + " order by " + sKey1 + " desc";
}
else
{
sql = "SELECT TOP 1 " + GetTableKeyColumn(sKey1, sKey2) + " FROM " + sTableMain + " WHERE " + sKey1 + " < " + keyMember1 + GetTableKey2Where(sTableMain, sKey2, keyMember2) + " AND " + sWhereSql + " order by " + sKey1 + " desc";
}
if (ConvertUtil.GetDBString(sKey2) == "")
{
keyMember1 = Linker.GetString(sql);
}
else
{
DataRow dataRow = Linker.GetDataRow(sql);
keyMember1 = ConvertUtil.GetDBString(dataRow[sKey1]);
keyMember2 = ConvertUtil.GetDBString(dataRow[sKey2]);
}
if (ConvertUtil.GetDBInt(keyMember1) <= 0)
{
GetLastBillD(ds, sTableMain, sMainSql, sTableDetail, sDetailSql, sWhereSql, sKey1, sKey2);
}
else
{
GetCurrentBill(ds, sTableMain, sMainSql, sTableDetail, sDetailSql, sKey1, keyMember1, sKey2, keyMember2);
}
}
public static void GetLastBillD(DataSet ds, string sTableMain, string sMainSql, string sTableDetail, string sDetailSql, string sWhereSql, string sKey1, string sKey2)
{
string sql = "";
if (ConvertUtil.GetDBString(sWhereSql) == "")
{
sql = "SELECT TOP 1 " + GetTableKeyColumn(sKey1, sKey2) + " FROM " + sTableMain + " order by " + sKey1 + " desc";
}
else
{
sql = "SELECT TOP 1 " + GetTableKeyColumn(sKey1, sKey2) + " FROM " + sTableMain + " WHERE " + sWhereSql + " order by " + sKey1 + " desc";
}
string dBString = "";
string str3 = "";
if (ConvertUtil.GetDBString(sKey2) == "")
{
dBString = Linker.GetString(sql);
}
else
{
DataRow dataRow = Linker.GetDataRow(sql);
dBString = ConvertUtil.GetDBString(dataRow[sKey1]);
str3 = ConvertUtil.GetDBString(dataRow[sKey2]);
}
GetCurrentBill(ds, sTableMain, sMainSql, sTableDetail, sDetailSql, sKey1, dBString, sKey2, str3);
}
public static string GetBillID(string sTableName, string sFieldName)
{
try
{
SqlParameter[] commandParameters = new SqlParameter[5];
commandParameters[0] = new SqlParameter("@i_tablename", SqlDbType.VarChar);
commandParameters[0].Direction = ParameterDirection.Input;
commandParameters[0].Value = sTableName;
commandParameters[1] = new SqlParameter("@i_fieldname", SqlDbType.VarChar);
commandParameters[1].Direction = ParameterDirection.Input;
commandParameters[1].Value = sFieldName;
commandParameters[2] = new SqlParameter("@i_generatedate", SqlDbType.VarChar);
commandParameters[2].Direction = ParameterDirection.Input;
commandParameters[2].Value = DateTime.Now.ToString("yyyy-MM-dd");
commandParameters[3] = new SqlParameter("@i_count", SqlDbType.Int);
commandParameters[3].Direction = ParameterDirection.Input;
commandParameters[3].Value = 1;
commandParameters[4] = new SqlParameter("@o_billcode", SqlDbType.VarChar);
commandParameters[4].Direction = ParameterDirection.Output;
commandParameters[4].Size = 500;
commandParameters[4].Value = "";
Linker.ExecuteNonQuery("proc_generatebillcode", commandParameters);
return(ConvertUtil.GetDBString(commandParameters[4].Value));
}
catch
{
return("");
}
}
private static string GetTableKeyColumn(string sKey1, string sKey2)
{
string str = " " + sKey1;
if (ConvertUtil.GetDBString(sKey2) != "")
{
str = str + ", " + sKey2 + " ";
}
return(str);
}
private static string GetTableKeyWhere(string sTableMain, string sKey1, string keyMember1, string sKey2, string keyMember2)
{
string str = sTableMain + "." + sKey1 + " = '" + keyMember1 + "'";
if (ConvertUtil.GetDBString(sKey2) != "")
{
string str3 = str;
str = str3 + " AND " + sTableMain + "." + sKey2 + " = '" + keyMember2 + "'";
}
return(str);
}
public static void GetCurrentBill(DataTable dt, string sTableMain, string sMainSql, string sKey1, string keyMember1, string sKey2, string keyMember2)
{
if (ConvertUtil.GetDBString(sMainSql) == "")
{
sMainSql = "SELECT * FROM " + sTableMain + " WHERE " + GetTableKeyWhere(sTableMain, sKey1, keyMember1, sKey2, keyMember2);
}
else
{
sMainSql = sMainSql + " WHERE " + GetTableKeyWhere(sTableMain, sKey1, keyMember1, sKey2, keyMember2);
}
Linker.FillDataTable(dt, sMainSql);
}
/// <summary>
/// 获取一个表的数据
/// </summary>
/// <param name="dt"></param>
/// <param name="sTableMain"></param>
/// <param name="sMainSql"></param>
/// <param name="sWhereSql"></param>
public static void GetMainTable(DataTable dt, string sTableMain, string sMainSql, string sWhereSql)
{
StringBuilder builder = new StringBuilder(5);
if (ConvertUtil.GetDBString(sMainSql) == "")
{
builder.Append(" SELECT * FROM ");
builder.Append(sTableMain);
}
else
{
builder.Append(sMainSql);
}
if (ConvertUtil.GetDBString(sWhereSql) != "")
{
builder.Append(" WHERE ");
builder.Append(sWhereSql);
}
Linker.FillDataTable(dt, builder.ToString());
}
public static void GetMainBillDataSet(DataSet ds, string sTableMain, string sMainSql, string sTableDetail, string sDetailSql, string sJoinSql, string sWhereSql)
{
string commandText = "";
if (ConvertUtil.GetDBString(sMainSql) == "")
{
commandText = " SELECT * FROM " + sTableMain + " WHERE 1 = 1 AND " + sWhereSql;
}
else
{
commandText = sMainSql + " WHERE 1 = 1 AND " + sWhereSql;
}
Linker.FillDataTable(ds.Tables[sTableMain], commandText);
if (ConvertUtil.GetDBString(sMainSql) == "")
{
commandText = "SELECT * FROM " + sTableDetail + sJoinSql + " WHERE 1 = 1 AND " + sWhereSql;
}
else
{
commandText = sDetailSql + sJoinSql + " WHERE 1 = 1 AND " + sWhereSql;
}
Linker.FillDataTable(ds.Tables[sTableDetail], commandText);
}
public static void GetCurrentBill(DataSet ds, string sTableMain, string sMainSql, string sTableDetail, string sDetailSql, string sKey1, string keyMember1, string sKey2, string keyMember2)
{
if (ConvertUtil.GetDBString(sMainSql) == "")
{
sMainSql = "SELECT * FROM " + sTableMain + " WHERE " + GetTableKeyWhere(sTableMain, sKey1, keyMember1, sKey2, keyMember2);
}
else
{
sMainSql = sMainSql + " WHERE " + GetTableKeyWhere(sTableMain, sKey1, keyMember1, sKey2, keyMember2);
}
Linker.FillDataTable(ds.Tables[sTableMain], sMainSql);
if (ConvertUtil.GetDBString(sTableDetail) != "")
{
if (ConvertUtil.GetDBString(sDetailSql) == "")
{
sDetailSql = "SELECT * FROM " + sTableDetail + " WHERE " + GetTableKeyWhere(sTableDetail, sKey1, keyMember1, sKey2, keyMember2);
}
else
{
sDetailSql = sDetailSql + " WHERE " + GetTableKeyWhere(sTableDetail, sKey1, keyMember1, sKey2, keyMember2);
}
Linker.FillDataTable(ds.Tables[sTableDetail], sDetailSql);
}
}
public static void CheckBillID(string MainTableName, DataRow drCurrent)
{
if ((((drCurrent != null) && (drCurrent.Table != null)) && drCurrent.Table.Columns.Contains("BillCode")) && (ConvertUtil.GetDBString(drCurrent["BillCode"]) != ""))
{
string sql = string.Concat(new object[] { "SELECT count(*) FROM ", MainTableName, " WHERE BillCode = '", drCurrent["BillCode"], "'" });
if (Linker.GetInt(sql) > 0)
{
drCurrent["BillCode"] = GetBillID(MainTableName);
drCurrent.EndEdit();
}
}
}