public string SavePendingReceivebleInvoice(InvoiceDataTypes.PendingInvoiceDataType _SaveData)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Insert Into tblpendingrecievebleinvoice ("
+ "CompanyID,"
+ "Customer,"
+ "InvoiceNo,"
+ "Description,"
+ "AccountID,"
+ "CurRate,"
+ "Dr,"
+ "FDr,"
+ "BillStatus,"
+ "AccPeriod,"
+ "TotalAmount,"
+ "TobeRcvDate,"
+ "InvoiceNoDate)"
+ " Values ("
+ "@CompanyID,"
+ "@Customer,"
+ "@InvoiceNo,"
+ "@Description,"
+ "@AccountID,"
+ "@CurRate,"
+ "@Dr,"
+ "@FDr,"
+ "@BillStatus,"
+ "@AccPeriod,"
+ "@TotalAmount,"
+ "@TobeRcvDate,"
+ "@InvoiceNoDate)";
try
{
oSqlCommand.Parameters.AddWithValue("@CompanyID", _SaveData.CompanyID);
oSqlCommand.Parameters.AddWithValue("@Customer", _SaveData.Customer);
oSqlCommand.Parameters.AddWithValue("@InvoiceNo", _SaveData.InvoiceNo);
oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description);
oSqlCommand.Parameters.AddWithValue("@AccountID", _SaveData.AccountID);
oSqlCommand.Parameters.AddWithValue("@CurRate", _SaveData.CurRate);
oSqlCommand.Parameters.AddWithValue("@Dr", _SaveData.Dr);
oSqlCommand.Parameters.AddWithValue("@FDr", _SaveData.FDr);
oSqlCommand.Parameters.AddWithValue("@BillStatus", _SaveData.BillStatus);
oSqlCommand.Parameters.AddWithValue("@AccPeriod", _SaveData.AccPeriod);
oSqlCommand.Parameters.AddWithValue("@TotalAmount", _SaveData.TotalAmount);
oSqlCommand.Parameters.AddWithValue("@TobeRcvDate", _SaveData.TobeRcvDate);
oSqlCommand.Parameters.AddWithValue("@InvoiceNoDate", _SaveData.InvoiceNoDate);
string respond = Mycommon.ExicuteAnyCommandAccount(sqlQuery, oSqlCommand, "Save PendingReceivebleInvoice");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string DoInvoice(InvoiceDataTypes.InvoiceDataType _SaveData)
{
Invoice MyInvoice = new Invoice(IsLocalLoging);
//===================================================================
try
{
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
string respond = "";
decimal FullPayment = 0;
if (CurCon.State == ConnectionState.Open)
{
CurCon.Close();
CurCon.Open();
}
else
{
CurCon.Open();
}
Mytrans = Mycommon.AccountConnection.BeginTransaction();
//===============================================================
string MainAccountRef = "", TrReference = "", PVNNumber = "";
MainAccountRef = GetAccReference(_SaveData.AccountID, CurCon);
int Counter = 0;
string DrAccRef = "";
string macAdd = MyGeneral.GetMACAddress();
if (_SaveData.InvoiceDtails.Count > 0)
{
foreach (InvoiceDataTypes.InvoiceDetailsDataType _OneData in _SaveData.InvoiceDtails)
{
AccountTransactionType.AccountTransactionDataType _OneTrans = new AccountTransactionType.AccountTransactionDataType();
string TransRefDr = "";
string AccRef = GetAccReference(_OneData.AccID, CurCon);
Counter += 1;
if (Counter < 5)
DrAccRef = DrAccRef + "," + AccRef;
_OneTrans.AccountID = _OneData.AccID;
_OneTrans.AccountYear = _SaveData.AccPeriod;
_OneTrans.ActualDate = _SaveData.InvoiceDate;
_OneTrans.CompanyID = _SaveData.CompanyID;
_OneTrans.Cr = _OneData.Cr;
_OneTrans.CurRate = _SaveData.CurRate;
_OneTrans.Description = _OneData.Description;
_OneTrans.Dr =0;
_OneTrans.FDr = 0;
_OneTrans.FCr = _OneData.FCr;
_OneTrans.MainRef = AccRef;
_OneTrans.PayMethod = 5;
_OneTrans.RelRef = MainAccountRef;
_OneTrans.TransType = 4;
_OneTrans.TrMachine = macAdd;
_OneTrans.TrUser = _SaveData.TrUser;
_OneTrans.VoucherID = _SaveData.InvoiceNo;
FullPayment = FullPayment + _OneData.Cr;
respond = SaveAccountTransaction(_OneTrans, CurCon, out TransRefDr);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
respond = UpdateTranseRefinReleventTable(4, false, TransRefDr, _SaveData.InvoiceNo , _OneData.ItemNo.ToString(), CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
respond = UpdateLastRefonAccount(_OneTrans.AccountID, AccRef, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
}
}
}
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
AccountTransactionType.AccountTransactionDataType _SaveTranData = new AccountTransactionType.AccountTransactionDataType();
_SaveTranData.AccountID = _SaveData.AccountID; //1
_SaveTranData.AccountYear = _SaveData.AccPeriod; //2
_SaveTranData.Description = _SaveData.Description; //3
_SaveTranData.CompanyID = _SaveData.CompanyID; //4
_SaveTranData.Cr = 0; //5
_SaveTranData.CurRate = _SaveData.CurRate; //6
_SaveTranData.Dr = _SaveData.Dr; //7
_SaveTranData.FCr = 0; //8
_SaveTranData.FDr = _SaveData.FDr; ; //9
_SaveTranData.MainRef = MainAccountRef; //10
_SaveTranData.PayMethod = 5; //11
_SaveTranData.TrID = TrReference; //12
_SaveTranData.TransType = 2; //13
_SaveTranData.RelRef = DrAccRef; //14
_SaveTranData.TrMachine = macAdd; //14
_SaveTranData.VoucherID = _SaveData.InvoiceNo ; //15
_SaveTranData.TrUser = _SaveData.TrUser; //16
_SaveTranData.ActualDate = _SaveData.InvoiceDate; //17
respond = SaveAccountTransaction(_SaveTranData, CurCon, out TrReference);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
respond = UpdateTranseRefinReleventTable(4, true, MainAccountRef, _SaveData.InvoiceNo, "", CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
respond = UpdateLastRefonAccount(_SaveData.AccountID, MainAccountRef, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
//=====================================
InvoiceDataTypes.PendingInvoiceDataType _PendingInvoice = new InvoiceDataTypes.PendingInvoiceDataType();
_PendingInvoice.AccountID = _SaveData.AccountID;
_PendingInvoice.AccPeriod = _SaveData.AccPeriod;
_PendingInvoice.BillStatus = 0;
_PendingInvoice.CompanyID = _SaveData.CompanyID;
_PendingInvoice.CurRate = _SaveData.CurRate;
_PendingInvoice.Customer = _SaveData.RcvFromID;
_PendingInvoice.Description = _SaveData.Description;
_PendingInvoice.Dr = _SaveData.Dr;
_PendingInvoice.FDr = _SaveData.FDr;
_PendingInvoice.InvoiceNo = _SaveData.InvoiceNo;
_PendingInvoice.InvoiceNoDate = _SaveData.InvoiceDate;
_PendingInvoice.RevievedAmount = 0;
_PendingInvoice.TobeRcvDate = _SaveData.TobeRcvDate;
_PendingInvoice.TotalAmount = _SaveData.Dr;
respond = MyInvoice.SavePending(_PendingInvoice, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
respond =MyInvoice .InvoiceAccountUpdated( _SaveData.InvoiceNo, _SaveData.TrUser,CurCon);
if (respond == "True")
{
Mytrans.Commit();
CurCon.Close();
return "True";
}
else
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
}
}
//=====================================
}
}
}
}
}
else
{
Mytrans.Rollback();
CurCon.Close();
return "No Details to save";
}
}
catch (Exception ex)
{
return ex.Message;
}
}
public string SavePending(InvoiceDataTypes.PendingInvoiceDataType _SaveData, MySqlConnection _ActCon)
{
string respond = "";
try
{
if (!ExistPendingReceivebleInvoice(_SaveData.Customer, _SaveData.InvoiceNo))
respond = SavePendingReceivebleInvoice(_SaveData, _ActCon);
else
respond = UpdatePendingReceivebleInvoice(_SaveData, _ActCon);
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string SaveInvoiceInTrans(InvoiceDataTypes.InvoiceDataType _SaveData,MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Insert Into tblrecievebleinvoice ("
+ "InvoiceNo,"
+ "Description,"
+ "AccountID,"
+ "CurRate,"
+ "Dr,"
+ "FDr,"
+ "InvoiceStatus,"
+ "TrRef,"
+ "TrUser,"
+ "TrDate,"
+ "TrTime,"
+ "AccPeriod,"
+ "CompanyID,"
+ "RcvFromCatID,"
+ "RcvFromID,"
+ "RcvAmount,"
+ "TobeRcvDate,"
+ "InvoiceDate)"
+ " Values ("
+ "@InvoiceNo,"
+ "@Description,"
+ "@AccountID,"
+ "@CurRate,"
+ "@Dr,"
+ "@FDr,"
+ "@InvoiceStatus,"
+ "@TrRef,"
+ "@TrUser,"
+ "curdate(),"
+ "curtime(),"
+ "@AccPeriod,"
+ "@CompanyID,"
+ "@RcvFromCatID,"
+ "@RcvFromID,"
+ "@RcvAmount,"
+ "@TobeRcvDate,"
+ "@InvoiceDate)";
try
{
oSqlCommand.Parameters.AddWithValue("@InvoiceNo", _SaveData.InvoiceNo);
oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description);
oSqlCommand.Parameters.AddWithValue("@AccountID", _SaveData.AccountID);
oSqlCommand.Parameters.AddWithValue("@CurRate", _SaveData.CurRate);
oSqlCommand.Parameters.AddWithValue("@Dr", _SaveData.Dr);
oSqlCommand.Parameters.AddWithValue("@FDr", _SaveData.FDr);
oSqlCommand.Parameters.AddWithValue("@InvoiceStatus", _SaveData.InvoiceStatus);
oSqlCommand.Parameters.AddWithValue("@TrRef", _SaveData.TrRef);
oSqlCommand.Parameters.AddWithValue("@TrUser", _SaveData.TrUser);
oSqlCommand.Parameters.AddWithValue("@AccPeriod", _SaveData.AccPeriod);
oSqlCommand.Parameters.AddWithValue("@CompanyID", _SaveData.CompanyID);
oSqlCommand.Parameters.AddWithValue("@RcvFromCatID", _SaveData.RcvFromCatID);
oSqlCommand.Parameters.AddWithValue("@RcvFromID", _SaveData.RcvFromID);
oSqlCommand.Parameters.AddWithValue("@RcvAmount", _SaveData.RcvAmount);
oSqlCommand.Parameters.AddWithValue("@TobeRcvDate", _SaveData.TobeRcvDate);
oSqlCommand.Parameters.AddWithValue("@InvoiceDate", _SaveData.InvoiceDate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon,"Save Invoice");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string UpdateInvoiceDetails(InvoiceDataTypes.InvoiceDetailsDataType _Update,MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tblrecievebleinvoicedetails Set "
+ "ItemNo=@ItemNo,"
+ "InvoiceNO=@InvoiceNO,"
+ "AccID=@AccID,"
+ "Description=@Description,"
+ "Cr=@Cr,"
+ "FCr=@FCr,"
+ "TrRef=@TrRef,"
+ "Vat=@Vat"
+ " Where 1=1 "
+ " and ItemNo=@ItemNo"
+ " and InvoiceNO=@InvoiceNO";
try
{
oSqlCommand.Parameters.AddWithValue("@ItemNo", _Update.ItemNo);
oSqlCommand.Parameters.AddWithValue("@InvoiceNO", _Update.InvoiceNO);
oSqlCommand.Parameters.AddWithValue("@AccID", _Update.AccID);
oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description);
oSqlCommand.Parameters.AddWithValue("@Cr", _Update.Cr);
oSqlCommand.Parameters.AddWithValue("@FCr", _Update.FCr);
oSqlCommand.Parameters.AddWithValue("@TrRef", _Update.TrRef);
oSqlCommand.Parameters.AddWithValue("@Vat", _Update.Vat);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Update InvoiceDetails");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string GetExistInvoiceDetails(int ItemNo, string InvoiceNO, out InvoiceDataTypes.InvoiceDetailsDataType _ExistData)
{
_ExistData = new InvoiceDataTypes.InvoiceDetailsDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "ItemNo,"
+ "InvoiceNO,"
+ "AccID,"
+ "Description,"
+ "Cr,"
+ "FCr,"
+ "TrRef,"
+ "Vat"
+ " from tblrecievebleinvoicedetails"
+ " Where 1=1 "
+ " and ItemNo=@ItemNo"
+ " and InvoiceNO=@InvoiceNO";
oSqlCommand.Parameters.AddWithValue("@ItemNo", ItemNo);
oSqlCommand.Parameters.AddWithValue("@InvoiceNO", InvoiceNO);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data InvoiceDetails");
if (r != null)
{
try
{
bool resp = false;
int inItemNo = 0;
resp = int.TryParse(r["ItemNo"].ToString(), out inItemNo);
_ExistData.ItemNo = inItemNo;
_ExistData.InvoiceNO = r["InvoiceNO"].ToString();
_ExistData.AccID = r["AccID"].ToString();
_ExistData.Description = r["Description"].ToString();
decimal deCr = 0;
resp = decimal.TryParse(r["Cr"].ToString(), out deCr);
_ExistData.Cr = deCr;
decimal deFCr = 0;
resp = decimal.TryParse(r["FCr"].ToString(), out deFCr);
_ExistData.FCr = deFCr;
_ExistData.TrRef = r["TrRef"].ToString();
decimal deVat = 0;
resp = decimal.TryParse(r["Vat"].ToString(), out deVat);
_ExistData.Vat = deVat;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string GetExistInvoice(string InvoiceNo, out InvoiceDataTypes.InvoiceDataType _ExistData)
{
_ExistData = new InvoiceDataTypes.InvoiceDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "InvoiceNo,"
+ "Description,"
+ "AccountID,"
+ "CurRate,"
+ "Dr,"
+ "FDr,"
+ "InvoiceStatus,"
+ "TrRef,"
+ "TrUser,"
+ "TrDate,"
+ "TrTime,"
+ "AccPeriod,"
+ "CompanyID,"
+ "RcvFromCatID,"
+ "RcvFromID,"
+ "RcvAmount,"
+ "TobeRcvDate,"
+ "InvoiceDate,"
+ "ApproveBy,"
+ "ApproveDate,"
+ "ApproveTime,"
+ "AcountedBy,"
+ "AccountDate,"
+ "AccountedTime"
+ " from tblrecievebleinvoice"
+ " Where 1=1 "
+ " and InvoiceNo=@InvoiceNo";
oSqlCommand.Parameters.AddWithValue("@InvoiceNo", InvoiceNo);
DataRow r = Mycommon.GetDataRowAccount(sqlQuery, oSqlCommand, "Get Exist data Invoice");
if (r != null)
{
try
{
bool resp = false;
_ExistData.InvoiceNo = r["InvoiceNo"].ToString();
_ExistData.Description = r["Description"].ToString();
_ExistData.AccountID = r["AccountID"].ToString();
decimal deCurRate = 0;
resp = decimal.TryParse(r["CurRate"].ToString(), out deCurRate);
_ExistData.CurRate = deCurRate;
decimal deDr = 0;
resp = decimal.TryParse(r["Dr"].ToString(), out deDr);
_ExistData.Dr = deDr;
decimal deFDr = 0;
resp = decimal.TryParse(r["FDr"].ToString(), out deFDr);
_ExistData.FDr = deFDr;
int inInvoiceStatus = 0;
resp = int.TryParse(r["InvoiceStatus"].ToString(), out inInvoiceStatus);
_ExistData.InvoiceStatus = inInvoiceStatus;
_ExistData.TrRef = r["TrRef"].ToString();
_ExistData.TrUser = r["TrUser"].ToString();
DateTime dtTrDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["TrDate"].ToString(), out dtTrDate);
if (resp)
_ExistData.TrDate = dtTrDate;
else
_ExistData.TrDate = new DateTime(1900, 1, 1);
_ExistData.TrTime = r["TrTime"].ToString();
int inAccPeriod = 0;
resp = int.TryParse(r["AccPeriod"].ToString(), out inAccPeriod);
_ExistData.AccPeriod = inAccPeriod;
int inCompanyID = 0;
resp = int.TryParse(r["CompanyID"].ToString(), out inCompanyID);
_ExistData.CompanyID = inCompanyID;
int inRcvFromCatID = 0;
resp = int.TryParse(r["RcvFromCatID"].ToString(), out inRcvFromCatID);
_ExistData.RcvFromCatID = inRcvFromCatID;
int inRcvFromID = 0;
resp = int.TryParse(r["RcvFromID"].ToString(), out inRcvFromID);
_ExistData.RcvFromID = inRcvFromID;
decimal deRcvAmount = 0;
resp = decimal.TryParse(r["RcvAmount"].ToString(), out deRcvAmount);
_ExistData.RcvAmount = deRcvAmount;
DateTime dtTobeRcvDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["TobeRcvDate"].ToString(), out dtTobeRcvDate);
if (resp)
_ExistData.TobeRcvDate = dtTobeRcvDate;
else
_ExistData.TobeRcvDate = new DateTime(1900, 1, 1);
DateTime dtInvoiceDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["InvoiceDate"].ToString(), out dtInvoiceDate);
if (resp)
_ExistData.InvoiceDate = dtInvoiceDate;
else
_ExistData.InvoiceDate = new DateTime(1900, 1, 1);
_ExistData.ApproveBy = r["ApproveBy"].ToString();
DateTime dtApproveDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["ApproveDate"].ToString(), out dtApproveDate);
if (resp)
_ExistData.ApproveDate = dtApproveDate;
else
_ExistData.ApproveDate = new DateTime(1900, 1, 1);
_ExistData.ApproveTime = r["ApproveTime"].ToString();
_ExistData.AcountedBy = r["AcountedBy"].ToString();
DateTime dtAccountDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["AccountDate"].ToString(), out dtAccountDate);
if (resp)
_ExistData.AccountDate = dtAccountDate;
else
_ExistData.AccountDate = new DateTime(1900, 1, 1);
_ExistData.AccountedTime = r["AccountedTime"].ToString();
List<InvoiceDataTypes.InvoiceDetailsDataType> _ExistDataList = new List<InvoiceDataTypes.InvoiceDetailsDataType>();
string respond = GetInvoiceDetailList(InvoiceNo, out _ExistDataList);
_ExistData.InvoiceDtails = _ExistDataList;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string SaveInvoiceDetails(InvoiceDataTypes.InvoiceDetailsDataType _SaveData,MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Insert Into tblrecievebleinvoicedetails ("
+ "ItemNo,"
+ "InvoiceNO,"
+ "AccID,"
+ "Description,"
+ "Cr,"
+ "FCr,"
+ "TrRef,"
+ "Vat)"
+ " Values ("
+ "@ItemNo,"
+ "@InvoiceNO,"
+ "@AccID,"
+ "@Description,"
+ "@Cr,"
+ "@FCr,"
+ "@TrRef,"
+ "@Vat)";
try
{
oSqlCommand.Parameters.AddWithValue("@ItemNo", _SaveData.ItemNo);
oSqlCommand.Parameters.AddWithValue("@InvoiceNO", _SaveData.InvoiceNO);
oSqlCommand.Parameters.AddWithValue("@AccID", _SaveData.AccID);
oSqlCommand.Parameters.AddWithValue("@Description", _SaveData.Description);
oSqlCommand.Parameters.AddWithValue("@Cr", _SaveData.Cr);
oSqlCommand.Parameters.AddWithValue("@FCr", _SaveData.FCr);
oSqlCommand.Parameters.AddWithValue("@TrRef", _SaveData.TrRef);
oSqlCommand.Parameters.AddWithValue("@Vat", _SaveData.Vat);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon, "Save InvoiceDetails");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string UpdateInvoiceInTrance(InvoiceDataTypes.InvoiceDataType _Update,MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tblrecievebleinvoice Set "
+ "InvoiceNo=@InvoiceNo,"
+ "Description=@Description,"
+ "AccountID=@AccountID,"
+ "CurRate=@CurRate,"
+ "Dr=@Dr,"
+ "FDr=@FDr,"
+ "InvoiceStatus=@InvoiceStatus,"
+ "TrRef=@TrRef,"
+ "TrUser=@TrUser,"
+ "TrDate=curdate(),"
+ "TrTime=curtime(),"
+ "AccPeriod=@AccPeriod,"
+ "CompanyID=@CompanyID,"
+ "RcvFromCatID=@RcvFromCatID,"
+ "RcvFromID=@RcvFromID,"
+ "RcvAmount=@RcvAmount,"
+ "TobeRcvDate=@TobeRcvDate,"
+ "InvoiceDate=@InvoiceDate"
+ " Where 1=1 "
+ " and InvoiceNo=@InvoiceNo";
try
{
oSqlCommand.Parameters.AddWithValue("@InvoiceNo", _Update.InvoiceNo);
oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description);
oSqlCommand.Parameters.AddWithValue("@AccountID", _Update.AccountID);
oSqlCommand.Parameters.AddWithValue("@CurRate", _Update.CurRate);
oSqlCommand.Parameters.AddWithValue("@Dr", _Update.Dr);
oSqlCommand.Parameters.AddWithValue("@FDr", _Update.FDr);
oSqlCommand.Parameters.AddWithValue("@InvoiceStatus", _Update.InvoiceStatus);
oSqlCommand.Parameters.AddWithValue("@TrRef", _Update.TrRef);
oSqlCommand.Parameters.AddWithValue("@TrUser", _Update.TrUser);
oSqlCommand.Parameters.AddWithValue("@TrDate", _Update.TrDate);
oSqlCommand.Parameters.AddWithValue("@TrTime", _Update.TrTime);
oSqlCommand.Parameters.AddWithValue("@AccPeriod", _Update.AccPeriod);
oSqlCommand.Parameters.AddWithValue("@CompanyID", _Update.CompanyID);
oSqlCommand.Parameters.AddWithValue("@RcvFromCatID", _Update.RcvFromCatID);
oSqlCommand.Parameters.AddWithValue("@RcvFromID", _Update.RcvFromID);
oSqlCommand.Parameters.AddWithValue("@RcvAmount", _Update.RcvAmount);
oSqlCommand.Parameters.AddWithValue("@TobeRcvDate", _Update.TobeRcvDate);
oSqlCommand.Parameters.AddWithValue("@InvoiceDate", _Update.InvoiceDate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand,_ActCon,"Update Invoice");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
public string Update(InvoiceDataTypes.InvoiceDataType _SaveData)
{
#region Initial Open
string respond = "";
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
if (CurCon.State == ConnectionState.Closed)
CurCon.Open();
Mytrans = Mycommon.AccountConnection.BeginTransaction();
MySqlCommand oSqlCommand = new MySqlCommand();
#endregion
if (ExistInvoice(_SaveData.InvoiceNo, CurCon))
{
respond = UpdateInvoiceInTrance(_SaveData, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
if (_SaveData.InvoiceDtails.Count > 0)
{
foreach (InvoiceDataTypes.InvoiceDetailsDataType _Oneitem in _SaveData.InvoiceDtails)
{
if (!ExistInvoiceDetails(_Oneitem.ItemNo, _Oneitem.InvoiceNO, CurCon))
{
respond = SaveInvoiceDetails(_Oneitem, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
}
else
{
respond = UpdateInvoiceDetails(_Oneitem, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
}
}
if (respond != "True")
{
Mytrans.Rollback();
CurCon.Close();
return respond;
}
else
{
Mytrans.Commit();
CurCon.Close();
return "True";
}
}
else
{
Mytrans.Rollback();
CurCon.Close();
return "No Details to save";
}
}
}
else
{
Mytrans.Rollback();
CurCon.Close();
return "Use Update Option";
}
}
public string GetExistPendingReceivebleInvoice(int Customer, string InvoiceNo, out InvoiceDataTypes.PendingInvoiceDataType _ExistData)
{
_ExistData = new InvoiceDataTypes.PendingInvoiceDataType();
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Select "
+ "CompanyID,"
+ "Customer,"
+ "InvoiceNo,"
+ "Description,"
+ "AccountID,"
+ "CurRate,"
+ "Dr,"
+ "FDr,"
+ "BillStatus,"
+ "AccPeriod,"
+ "TotalAmount,"
+ "RevievedAmount,"
+ "TobeRcvDate,"
+ "InvoiceNoDate"
+ " from tblpendingrecievebleinvoice"
+ " Where 1=1 "
+ " and Customer=@Customer"
+ " and InvoiceNo=@InvoiceNo";
oSqlCommand.Parameters.AddWithValue("@Customer", Customer);
oSqlCommand.Parameters.AddWithValue("@InvoiceNo", InvoiceNo);
DataRow r = Mycommon.GetDataRow(sqlQuery, oSqlCommand, null, "Get Exist data PendingReceivebleInvoice");
if (r != null)
{
try
{
bool resp = false;
int inCompanyID = 0;
resp = int.TryParse(r["CompanyID"].ToString(), out inCompanyID);
_ExistData.CompanyID = inCompanyID;
int inCustomer = 0;
resp = int.TryParse(r["Customer"].ToString(), out inCustomer);
_ExistData.Customer = inCustomer;
_ExistData.InvoiceNo = r["InvoiceNo"].ToString();
_ExistData.Description = r["Description"].ToString();
_ExistData.AccountID = r["AccountID"].ToString();
decimal deCurRate = 0;
resp = decimal.TryParse(r["CurRate"].ToString(), out deCurRate);
_ExistData.CurRate = deCurRate;
decimal deDr = 0;
resp = decimal.TryParse(r["Dr"].ToString(), out deDr);
_ExistData.Dr = deDr;
decimal deFDr = 0;
resp = decimal.TryParse(r["FDr"].ToString(), out deFDr);
_ExistData.FDr = deFDr;
int inBillStatus = 0;
resp = int.TryParse(r["BillStatus"].ToString(), out inBillStatus);
_ExistData.BillStatus = inBillStatus;
int inAccPeriod = 0;
resp = int.TryParse(r["AccPeriod"].ToString(), out inAccPeriod);
_ExistData.AccPeriod = inAccPeriod;
decimal deTotalAmount = 0;
resp = decimal.TryParse(r["TotalAmount"].ToString(), out deTotalAmount);
_ExistData.TotalAmount = deTotalAmount;
decimal deRevievedAmount = 0;
resp = decimal.TryParse(r["RevievedAmount"].ToString(), out deRevievedAmount);
_ExistData.RevievedAmount = deRevievedAmount;
DateTime dtTobeRcvDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["TobeRcvDate"].ToString(), out dtTobeRcvDate);
if (resp)
_ExistData.TobeRcvDate = dtTobeRcvDate;
else
_ExistData.TobeRcvDate = new DateTime(1900, 1, 1);
DateTime dtInvoiceNoDate = new DateTime(1900, 1, 1);
resp = DateTime.TryParse(r["InvoiceNoDate"].ToString(), out dtInvoiceNoDate);
if (resp)
_ExistData.InvoiceNoDate = dtInvoiceNoDate;
else
_ExistData.InvoiceNoDate = new DateTime(1900, 1, 1);
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}
else
return "data not found ";
}
public string UpdatePendingReceivebleInvoice(InvoiceDataTypes.PendingInvoiceDataType _Update, MySqlConnection _ActCon)
{
MySqlCommand oSqlCommand = new MySqlCommand();
string sqlQuery = "Update tblpendingrecievebleinvoice Set "
+ "CompanyID=@CompanyID,"
+ "Customer=@Customer,"
+ "InvoiceNo=@InvoiceNo,"
+ "Description=@Description,"
+ "AccountID=@AccountID,"
+ "CurRate=@CurRate,"
+ "Dr=@Dr,"
+ "FDr=@FDr,"
+ "BillStatus=@BillStatus,"
+ "AccPeriod=@AccPeriod,"
+ "TotalAmount=@TotalAmount,"
+ "TobeRcvDate=@TobeRcvDate,"
+ "InvoiceNoDate=@InvoiceNoDate"
+ " Where 1=1 "
+ " and Customer=@Customer"
+ " and InvoiceNo=@InvoiceNo";
try
{
oSqlCommand.Parameters.AddWithValue("@CompanyID", _Update.CompanyID);
oSqlCommand.Parameters.AddWithValue("@Customer", _Update.Customer);
oSqlCommand.Parameters.AddWithValue("@InvoiceNo", _Update.InvoiceNo);
oSqlCommand.Parameters.AddWithValue("@Description", _Update.Description);
oSqlCommand.Parameters.AddWithValue("@AccountID", _Update.AccountID);
oSqlCommand.Parameters.AddWithValue("@CurRate", _Update.CurRate);
oSqlCommand.Parameters.AddWithValue("@Dr", _Update.Dr);
oSqlCommand.Parameters.AddWithValue("@FDr", _Update.FDr);
oSqlCommand.Parameters.AddWithValue("@BillStatus", _Update.BillStatus);
oSqlCommand.Parameters.AddWithValue("@AccPeriod", _Update.AccPeriod);
oSqlCommand.Parameters.AddWithValue("@TotalAmount", _Update.TotalAmount);
oSqlCommand.Parameters.AddWithValue("@TobeRcvDate", _Update.TobeRcvDate);
oSqlCommand.Parameters.AddWithValue("@InvoiceNoDate", _Update.InvoiceNoDate);
string respond = Mycommon.ExicuteAnyCommandAccountWithTrans(sqlQuery, oSqlCommand, _ActCon ,"Update PendingReceivebleInvoice");
return respond;
}
catch (Exception ex)
{
return ex.Message;
}
}
private string SetDatatoClass(out InvoiceDataTypes.InvoiceDataType _saveData)
{
_saveData = new InvoiceDataTypes.InvoiceDataType();
try
{
decimal TotalLKR = 0, TotalUSD = 0;
bool resp = decimal.TryParse(lblTotalAmount.Text, out TotalLKR);
resp = decimal.TryParse(lblFcr.Text, out TotalUSD);
string supid = MyCommon.GetSelectedID(cmbSupplier, true);
int SupID1 = int.Parse(supid);
_saveData.AccPeriod = Program.AccountStatic.CurrentAccPeriod;
_saveData.AccountID = lblAccnumber.Text;
_saveData.CompanyID = Program.AccountStatic.CompanyID;
_saveData.CurRate = decimal.Parse(lblExchangerate.Text);
_saveData.Description = txtDescription.Text;
_saveData.Dr = TotalLKR;
_saveData.FDr = TotalUSD;
_saveData.InvoiceDate = dtpInvoiceDate.Value;
_saveData.InvoiceNo = cmbGRN.Text;
_saveData.InvoiceStatus = 0;
_saveData.RcvAmount = TotalLKR;
_saveData.RcvFromCatID = 0;
_saveData.RcvFromID = SupID1;
_saveData.TobeRcvDate = DateTime.Parse(lblDueDate.Text);
_saveData.TrUser = Program.AccountStatic.UserName;
List<InvoiceDataTypes.InvoiceDetailsDataType> _DetailList = new List<InvoiceDataTypes.InvoiceDetailsDataType>();
string respond = SetDetailsToClass(out _DetailList);
_saveData.InvoiceDtails = _DetailList;
return "True";
}
catch (Exception ex)
{
return ex.Message;
}
}