public PettyCashmodel GetCashmodel(Guid id)
{
string queryString = $@" SELECT
GUID, CaseID, Date,class,pay,Event,invoiceCode
FROM 零用金
WHERE GUID = @id";
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@id", id);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
PettyCashmodel model = null;
while (reader.Read())
{
model = new PettyCashmodel();
model.ID = (Guid)reader["GUID"];
model.CaseID = (int)reader["CaseID"];
model.Date = (DateTime)reader["Date"];
model.Class = (string)reader["class"];
model.pay = (decimal)reader["pay"];
model.Event = (string)reader["Event"];
model.invoiceCode = (string)reader["invoiceCode"];
}
reader.Close();
return(model);
}
catch (Exception ex)
{
throw;
}
}
}
public static void Updata(PettyCashmodel model, Guid GUID)
{
string queryString =
$@" UPDATE 零用金
SET CaseID = @CaseID, date = @date, class = @cla , pay = @pay, Event = @Evn, invoiceCode = @invo , Updatetime = @Updatetime
WHERE GUID = @GUID";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@GUID", GUID),
new SqlParameter("@CaseID", model.CaseID),
new SqlParameter("@date", model.Date),
new SqlParameter("@cla", model.Class),
new SqlParameter("@pay", model.pay),
new SqlParameter("@Evn", model.Event),
new SqlParameter("@invo", model.invoiceCode),
new SqlParameter("@Updatetime", DateTime.Now)
};
PettyCashmanager.ExecuteNonQuery(queryString, parameters);
}
public static void insert(PettyCashmodel model)
{
string queryString =
$@" INSERT INTO 零用金
(GUID,CaseID,Date,class,pay,Event,invoiceCode,Createtime,Isdelete)
VALUES
(@GUID,@CaseID,@date,@cla,@pay,@Evn,@invo,@Createtime,@Isdelete)";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@GUID", Guid.NewGuid()),
new SqlParameter("@CaseID", model.CaseID),
new SqlParameter("@date", model.Date),
new SqlParameter("@cla", model.Class),
new SqlParameter("@pay", model.pay),
new SqlParameter("@Evn", model.Event),
new SqlParameter("@invo", model.invoiceCode),
new SqlParameter("@Createtime", DateTime.Now),
new SqlParameter("@Isdelete", false)
};
PettyCashmanager.ExecuteNonQuery(queryString, parameters);
}
//搜尋跟顯示Table資料
public List <PettyCashmodel> ViewAllPettyCash(
string DDclass, string keyword, out int totalSize, int currentSize = 1, int pageSize = 8)
{
//----- Process filter conditions -----
List <string> conditions = new List <string>();
if (!string.IsNullOrEmpty(DDclass) && !string.IsNullOrEmpty(keyword))
{
conditions.Add($" {DDclass} LIKE '%' + @keyword + '%'");
}
string filterConditions =
(conditions.Count > 0)
? (" WHERE " + string.Join(" AND ", conditions))
: string.Empty;
//----- Process filter conditions -----
string Query =
$@"
SELECT TOP {8} * FROM
(
SELECT *, ROW_NUMBER() OVER(ORDER BY CaseID) AS ROWNUM
FROM 零用金
{filterConditions}) a
WHERE ROWNUM > {pageSize * (currentSize - 1)} AND Isdelete='false';";
/*搜尋從最上面數來8筆資料
* (創建一個ROWNUM條列 依照CaseID排序)
* 條件(影響總比數):ROWNUM的 > (一頁內的資料數 * (連結頁面 - 1) = 起始資料第幾項 ) AND 被刪除資料是false*/
string countQuery =
$@"
SELECT
COUNT (CaseID)
FROM 零用金
{filterConditions}
";
List <SqlParameter> dbParameters = new List <SqlParameter>();
if (!string.IsNullOrWhiteSpace(DDclass))
{
dbParameters.Add(new SqlParameter("@DDclass", DDclass));
}
if (!string.IsNullOrWhiteSpace(keyword))
{
dbParameters.Add(new SqlParameter("@keyword", keyword));
}
var dt = this.GetDataTable(Query, dbParameters);
List <PettyCashmodel> list = new List <PettyCashmodel>();
foreach (DataRow dr in dt.Rows)
{
PettyCashmodel model = new PettyCashmodel();
model.ID = (Guid)dr["GUID"];
model.CaseID = (int)dr["CaseID"];
model.Date = (DateTime)dr["Date"];
model.Class = (string)dr["class"];
model.pay = (decimal)dr["pay"];
model.Event = (string)dr["Event"];
model.invoiceCode = (string)dr["invoiceCode"];
list.Add(model);
}
int?totalSize2 = this.GetScale(countQuery, dbParameters) as int?;
totalSize = (totalSize2.HasValue) ? totalSize2.Value : 0;
return(list);
}
