public ClosingDetails Details(long ClosingID)
{
try
{
string SQL=SQLSelect() + "WHERE ClosingID = @ClosingID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmClosingID = new MySqlParameter("@ClosingID",MySqlDbType.Int16);
prmClosingID.Value = ClosingID;
cmd.Parameters.Add(prmClosingID);
MySqlDataReader myReader = base.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
ClosingDetails Details = new ClosingDetails();
while (myReader.Read())
{
Details.ClosingID = ClosingID;
Details.ClosingNo = "" + myReader["ClosingNo"].ToString();
Details.ClosingDate = myReader.GetDateTime("ClosingDate");
Details.SupplierID = myReader.GetInt64("SupplierID");
Details.SupplierCode = "" + myReader["SupplierCode"].ToString();
Details.SupplierContact = "" + myReader["SupplierContact"].ToString();
Details.SupplierAddress = "" + myReader["SupplierAddress"].ToString();
Details.SupplierTelephoneNo = "" + myReader["SupplierTelephoneNo"].ToString();
Details.SupplierModeOfTerms = myReader.GetInt16("SupplierModeofTerms");
Details.SupplierTerms = myReader.GetInt16("SupplierTerms");
Details.RequiredDeliveryDate = myReader.GetDateTime("RequiredDeliveryDate");
Details.BranchID = myReader.GetInt16("BranchID");
Details.BranchCode = "" + myReader["BranchCode"].ToString();
Details.BranchName = "" + myReader["BranchName"].ToString();
Details.BranchAddress = "" + myReader["BranchAddress"].ToString();
Details.TransferredByID = myReader.GetInt64("TransferredByID");
Details.ClosingSubTotal = myReader.GetDecimal("ClosingSubTotal");
Details.ClosingDiscount = myReader.GetDecimal("ClosingDiscount");
Details.ClosingVAT = myReader.GetDecimal("ClosingVAT");
Details.ClosingVatableAmount = myReader.GetDecimal("ClosingVatableAmount");
Details.ClosingStatus = (ClosingStatus)Enum.Parse(typeof(ClosingStatus), myReader.GetString("ClosingStatus"));
Details.ClosingRemarks = "" + myReader["ClosingRemarks"].ToString();
Details.SupplierDRNo = "" + myReader["SupplierDRNo"].ToString();
Details.DeliveryDate = myReader.GetDateTime("DeliveryDate");
}
myReader.Close();
return Details;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public long Insert(ClosingDetails Details)
{
try
{
string SQL = "INSERT INTO tblClosing (" +
"ClosingNo, " +
"ClosingDate, " +
"SupplierID, " +
"SupplierCode, " +
"SupplierContact, " +
"SupplierAddress, " +
"SupplierTelephoneNo, " +
"SupplierModeOfTerms, " +
"SupplierTerms, " +
"RequiredDeliveryDate, " +
"BranchID, " +
"TransferredByID, " +
"ClosingSubTotal, " +
"ClosingDiscount, " +
"ClosingVAT, " +
"ClosingVatableAmount, " +
"ClosingStatus, " +
"ClosingRemarks " +
") VALUES (" +
"@ClosingNo, " +
"@ClosingDate, " +
"@SupplierID, " +
"@SupplierCode, " +
"@SupplierContact, " +
"@SupplierAddress, " +
"@SupplierTelephoneNo, " +
"@SupplierModeOfTerms, " +
"@SupplierTerms, " +
"@RequiredDeliveryDate, " +
"@BranchID, " +
"@TransferredByID, " +
"@ClosingSubTotal, " +
"@ClosingDiscount, " +
"@ClosingVAT, " +
"@ClosingVatableAmount, " +
"@ClosingStatus, " +
"@ClosingRemarks " +
");";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmClosingNo = new MySqlParameter("@ClosingNo",MySqlDbType.String);
prmClosingNo.Value = Details.ClosingNo;
cmd.Parameters.Add(prmClosingNo);
MySqlParameter prmClosingDate = new MySqlParameter("@ClosingDate",MySqlDbType.DateTime);
prmClosingDate.Value = Details.ClosingDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmClosingDate);
MySqlParameter prmSupplierID = new MySqlParameter("@SupplierID",MySqlDbType.Int64);
prmSupplierID.Value = Details.SupplierID;
cmd.Parameters.Add(prmSupplierID);
MySqlParameter prmSupplierCode = new MySqlParameter("@SupplierCode",MySqlDbType.String);
prmSupplierCode.Value = Details.SupplierCode;
cmd.Parameters.Add(prmSupplierCode);
MySqlParameter prmSupplierContact = new MySqlParameter("@SupplierContact",MySqlDbType.String);
prmSupplierContact.Value = Details.SupplierContact;
cmd.Parameters.Add(prmSupplierContact);
MySqlParameter prmSupplierAddress = new MySqlParameter("@SupplierAddress",MySqlDbType.String);
prmSupplierAddress.Value = Details.SupplierAddress;
cmd.Parameters.Add(prmSupplierAddress);
MySqlParameter prmSupplierTelephoneNo = new MySqlParameter("@SupplierTelephoneNo",MySqlDbType.String);
prmSupplierTelephoneNo.Value = Details.SupplierTelephoneNo;
cmd.Parameters.Add(prmSupplierTelephoneNo);
MySqlParameter prmSupplierModeOfTerms = new MySqlParameter("@SupplierModeOfTerms",MySqlDbType.Int16);
prmSupplierModeOfTerms.Value = Details.SupplierModeOfTerms;
cmd.Parameters.Add(prmSupplierModeOfTerms);
MySqlParameter prmSupplierTerms = new MySqlParameter("@SupplierTerms",MySqlDbType.Int16);
prmSupplierTerms.Value = Details.SupplierTerms;
cmd.Parameters.Add(prmSupplierTerms);
MySqlParameter prmRequiredDeliveryDate = new MySqlParameter("@RequiredDeliveryDate",MySqlDbType.DateTime);
prmRequiredDeliveryDate.Value = Details.RequiredDeliveryDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmRequiredDeliveryDate);
MySqlParameter prmBranchID = new MySqlParameter("@BranchID",MySqlDbType.Int16);
prmBranchID.Value = Details.BranchID;
cmd.Parameters.Add(prmBranchID);
MySqlParameter prmTransferredByID = new MySqlParameter("@TransferredByID",MySqlDbType.Int64);
prmTransferredByID.Value = Details.TransferredByID;
cmd.Parameters.Add(prmTransferredByID);
MySqlParameter prmClosingSubTotal = new MySqlParameter("@ClosingSubTotal",MySqlDbType.Decimal);
prmClosingSubTotal.Value = Details.ClosingSubTotal;
cmd.Parameters.Add(prmClosingSubTotal);
MySqlParameter prmClosingDiscount = new MySqlParameter("@ClosingDiscount",MySqlDbType.Decimal);
prmClosingDiscount.Value = Details.ClosingDiscount;
cmd.Parameters.Add(prmClosingDiscount);
MySqlParameter prmClosingVAT = new MySqlParameter("@ClosingVAT",MySqlDbType.Decimal);
prmClosingVAT.Value = Details.ClosingVAT;
cmd.Parameters.Add(prmClosingVAT);
MySqlParameter prmClosingVatableAmount = new MySqlParameter("@ClosingVatableAmount",MySqlDbType.Decimal);
prmClosingVatableAmount.Value = Details.ClosingVatableAmount;
cmd.Parameters.Add(prmClosingVatableAmount);
MySqlParameter prmClosingStatus = new MySqlParameter("@ClosingStatus",MySqlDbType.Int16);
prmClosingStatus.Value = Details.ClosingStatus.ToString("d");
cmd.Parameters.Add(prmClosingStatus);
MySqlParameter prmClosingRemarks = new MySqlParameter("@ClosingRemarks",MySqlDbType.String);
prmClosingRemarks.Value = Details.ClosingRemarks;
cmd.Parameters.Add(prmClosingRemarks);
base.ExecuteNonQuery(cmd);
SQL = "SELECT LAST_INSERT_ID();";
cmd.Parameters.Clear();
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
Int64 iID = 0;
foreach (System.Data.DataRow dr in dt.Rows)
{
iID = Int64.Parse(dr[0].ToString());
}
return iID;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void Update(ClosingDetails Details)
{
try
{
string SQL= "UPDATE tblClosing SET " +
"ClosingNo = @ClosingNo, " +
"ClosingDate = @ClosingDate, " +
"SupplierID = @SupplierID, " +
"SupplierCode = @SupplierCode, " +
"SupplierContact = @SupplierContact, " +
"SupplierAddress = @SupplierAddress, " +
"SupplierTelephoneNo = @SupplierTelephoneNo, " +
"SupplierModeOfTerms = @SupplierModeOfTerms, " +
"SupplierTerms = @SupplierTerms, " +
"RequiredDeliveryDate = @RequiredDeliveryDate, " +
"BranchID = @BranchID, " +
"TransferredByID = @TransferredByID, " +
// "ClosingSubTotal = @ClosingSubTotal, " +
// "ClosingDiscount = @ClosingDiscount, " +
// "ClosingVAT = @ClosingVAT, " +
// "ClosingVatableAmount = @ClosingVatableAmount, " +
// "ClosingStatus = @ClosingStatus, " +
"ClosingRemarks = @ClosingRemarks " +
"WHERE ClosingID = @ClosingID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmClosingNo = new MySqlParameter("@ClosingNo",MySqlDbType.String);
prmClosingNo.Value = Details.ClosingNo;
cmd.Parameters.Add(prmClosingNo);
MySqlParameter prmClosingDate = new MySqlParameter("@ClosingDate",MySqlDbType.DateTime);
prmClosingDate.Value = Details.ClosingDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmClosingDate);
MySqlParameter prmSupplierID = new MySqlParameter("@SupplierID",MySqlDbType.Int64);
prmSupplierID.Value = Details.SupplierID;
cmd.Parameters.Add(prmSupplierID);
MySqlParameter prmSupplierCode = new MySqlParameter("@SupplierCode",MySqlDbType.String);
prmSupplierCode.Value = Details.SupplierCode;
cmd.Parameters.Add(prmSupplierCode);
MySqlParameter prmSupplierContact = new MySqlParameter("@SupplierContact",MySqlDbType.String);
prmSupplierContact.Value = Details.SupplierContact;
cmd.Parameters.Add(prmSupplierContact);
MySqlParameter prmSupplierAddress = new MySqlParameter("@SupplierAddress",MySqlDbType.String);
prmSupplierAddress.Value = Details.SupplierAddress;
cmd.Parameters.Add(prmSupplierAddress);
MySqlParameter prmSupplierTelephoneNo = new MySqlParameter("@SupplierTelephoneNo",MySqlDbType.String);
prmSupplierTelephoneNo.Value = Details.SupplierTelephoneNo;
cmd.Parameters.Add(prmSupplierTelephoneNo);
MySqlParameter prmSupplierModeOfTerms = new MySqlParameter("@SupplierModeOfTerms",MySqlDbType.Int16);
prmSupplierModeOfTerms.Value = Details.SupplierModeOfTerms;
cmd.Parameters.Add(prmSupplierModeOfTerms);
MySqlParameter prmSupplierTerms = new MySqlParameter("@SupplierTerms",MySqlDbType.Int16);
prmSupplierTerms.Value = Details.SupplierTerms;
cmd.Parameters.Add(prmSupplierTerms);
MySqlParameter prmRequiredDeliveryDate = new MySqlParameter("@RequiredDeliveryDate",MySqlDbType.DateTime);
prmRequiredDeliveryDate.Value = Details.RequiredDeliveryDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmRequiredDeliveryDate);
MySqlParameter prmBranchID = new MySqlParameter("@BranchID",MySqlDbType.Int16);
prmBranchID.Value = Details.BranchID;
cmd.Parameters.Add(prmBranchID);
MySqlParameter prmTransferredByID = new MySqlParameter("@TransferredByID",MySqlDbType.Int64);
prmTransferredByID.Value = Details.TransferredByID;
cmd.Parameters.Add(prmTransferredByID);
// MySqlParameter prmClosingSubTotal = new MySqlParameter("@ClosingSubTotal",MySqlDbType.Decimal);
// prmClosingSubTotal.Value = Details.ClosingSubTotal;
// cmd.Parameters.Add(prmClosingSubTotal);
//
// MySqlParameter prmClosingDiscount = new MySqlParameter("@ClosingDiscount",MySqlDbType.Decimal);
// prmClosingDiscount.Value = Details.ClosingDiscount;
// cmd.Parameters.Add(prmClosingDiscount);
//
// MySqlParameter prmClosingVAT = new MySqlParameter("@ClosingVAT",MySqlDbType.Decimal);
// prmClosingVAT.Value = Details.ClosingVAT;
// cmd.Parameters.Add(prmClosingVAT);
//
// MySqlParameter prmClosingVatableAmount = new MySqlParameter("@ClosingVatableAmount",MySqlDbType.Decimal);
// prmClosingVatableAmount.Value = Details.ClosingVatableAmount;
// cmd.Parameters.Add(prmClosingVatableAmount);
//
// MySqlParameter prmClosingStatus = new MySqlParameter("@ClosingStatus",MySqlDbType.Int16);
// prmClosingStatus.Value = Details.ClosingStatus.ToString("d");
// cmd.Parameters.Add(prmClosingStatus);
MySqlParameter prmClosingRemarks = new MySqlParameter("@ClosingRemarks",MySqlDbType.String);
prmClosingRemarks.Value = Details.ClosingRemarks;
cmd.Parameters.Add(prmClosingRemarks);
MySqlParameter prmClosingID = new MySqlParameter("@ClosingID",MySqlDbType.Int64);
prmClosingID.Value = Details.ClosingID;
cmd.Parameters.Add(prmClosingID);
base.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}