protected void lbDelete_Click(object sender, EventArgs e)
{
LinkButton linkButton = new LinkButton();
linkButton = (LinkButton)sender;
bool result = Pos_TransactionTypeManager.DeletePos_TransactionType(Convert.ToInt32(linkButton.CommandArgument));
showPos_TransactionTypeGrid();
}
protected void btnReceived_Click(object sender, EventArgs e)
{
int Pos_TransactionMasterID = int.Parse(Request.QueryString["Pos_TransactionMasterID"]);
Pos_TransactionMaster pos_TransactionMaster = Pos_TransactionMasterManager.GetPos_TransactionMasterByID(Pos_TransactionMasterID);
Pos_TransactionType transactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(pos_TransactionMaster.Pos_TransactionTypeID);
string sql = "Declare @Count int; ";
sql += "update Pos_TransactionMaster set ExtraField5='' where Pos_TransactionMasterID=" + Pos_TransactionMasterID + ";";
List <Pos_Product> items = new List <Pos_Product>();
items = Pos_ProductManager.GetAllPos_ProductsByTrasactionMasterID(Pos_TransactionMasterID);
foreach (Pos_Product pos_Transaction in items)
{
if (pos_TransactionMaster.WorkSatationID == 1 && transactionType.CentralStockFormula != "0")
{
//For head office Central Stock
sql += "Update Pos_Product set ExtraField1 =(cast(ExtraField1 as decimal(10,2)) + ((" + transactionType.CentralStockFormula + ")*" + pos_Transaction.ExtraField1 + ")) where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + ";";
}
else
if (pos_TransactionMaster.WorkSatationID != 1 && transactionType.ShowRoomFormula != "0")
{
sql += @"
set @Count=
(
select COUNT(*) from Pos_WorkStationStock
where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @" and WorkStationID=" + pos_TransactionMaster.WorkSatationID + @"
)
if @Count = 0
BEGIN
INSERT INTO [Pos_WorkStationStock]
([WorkStationID]
,[ProductID]
,[Stock])
VALUES(" + pos_TransactionMaster.WorkSatationID + @"," + pos_Transaction.Pos_ProductID.ToString() + @"," + pos_Transaction.ExtraField1 + @");
END
ELSE
BEGIN
Update Pos_WorkStationStock set Stock += ((+1)*" + pos_Transaction.ExtraField1 + @") where WorkStationID=" + pos_TransactionMaster.WorkSatationID + @" and ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
END;
";
}
CommonManager.SQLExec(sql);
}
Response.Redirect("TransactionPrint.aspx?Pos_TransactionMasterID=" + Pos_TransactionMasterID);
}
private void showPos_TransactionTypeData()
{
Pos_TransactionType pos_TransactionType = new Pos_TransactionType();
pos_TransactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(Int32.Parse(Request.QueryString["pos_TransactionTypeID"]));
txtTransactionTypeName.Text = pos_TransactionType.TransactionTypeName;
txtCentralStockFormula.Text = pos_TransactionType.CentralStockFormula;
txtShowRoomFormula.Text = pos_TransactionType.ShowRoomFormula;
txtExtraField1.Text = pos_TransactionType.ExtraField1;
txtExtraField2.Text = pos_TransactionType.ExtraField2;
txtExtraField3.Text = pos_TransactionType.ExtraField3;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
Pos_TransactionType pos_TransactionType = new Pos_TransactionType();
pos_TransactionType.TransactionTypeName = txtTransactionTypeName.Text;
pos_TransactionType.CentralStockFormula = txtCentralStockFormula.Text;
pos_TransactionType.ShowRoomFormula = txtShowRoomFormula.Text;
pos_TransactionType.ExtraField1 = txtExtraField1.Text;
pos_TransactionType.ExtraField2 = txtExtraField2.Text;
pos_TransactionType.ExtraField3 = txtExtraField3.Text;
int resutl = Pos_TransactionTypeManager.InsertPos_TransactionType(pos_TransactionType);
Response.Redirect("AdminPos_TransactionTypeDisplay.aspx");
}
private void loadPos_TransactionType()
{
ListItem li = new ListItem("Select Pos_TransactionType...", "0");
ddlPos_TransactionType.Items.Add(li);
List <Pos_TransactionType> pos_TransactionTypes = new List <Pos_TransactionType>();
pos_TransactionTypes = Pos_TransactionTypeManager.GetAllPos_TransactionTypes();
foreach (Pos_TransactionType pos_TransactionType in pos_TransactionTypes)
{
ListItem item = new ListItem(pos_TransactionType.Pos_TransactionTypeName.ToString(), pos_TransactionType.Pos_TransactionTypeID.ToString());
ddlPos_TransactionType.Items.Add(item);
}
}
private void initialLoad()
{
txtDate.Text = DateTime.Today.ToString("dd MMM yyyy");
int transactionTypeID = int.Parse(Request.QueryString["TransactionTypeID"]);
try
{
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(transactionTypeID).TransactionTypeName;
string sql = "select case Count(TransactionID) when 0 then 1 Else (MAX(TransactionID)+1) end from Pos_TransactionMaster where Pos_TransactionTypeID=" + transactionTypeID.ToString() + " and WorkSatationID=1";
lblVoucherType.Text += "(" + CommonManager.SQLExec(sql).Tables[0].Rows[0][0].ToString() + ")";
}
catch (Exception ex)
{
lblVoucherType.Text = "Barcode Generate";
}
}
private void initailaData()
{
loadACC_ChartOfAccountLabel4();
txtFromDate.Text = DateTime.Now.ToString("dd MMM yyyy");
txtToDate.Text = DateTime.Now.ToString("dd MMM yyyy");
//txtStockDate.Text = DateTime.Now.ToString("dd MMM yyyy");
List <Pos_TransactionType> allTransactionType = Pos_TransactionTypeManager.GetAllPos_TransactionTypes();
foreach (Pos_TransactionType type in allTransactionType)
{
if (type.ExtraField2 == "SR")
{
rbtnTransactionType.Items.Add(new ListItem(type.TransactionTypeName, type.Pos_TransactionTypeID.ToString()));
}
}
rbtnTransactionType.SelectedValue = "13";
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
Pos_TransactionType pos_TransactionType = new Pos_TransactionType();
pos_TransactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(Int32.Parse(Request.QueryString["pos_TransactionTypeID"]));
Pos_TransactionType tempPos_TransactionType = new Pos_TransactionType();
tempPos_TransactionType.Pos_TransactionTypeID = pos_TransactionType.Pos_TransactionTypeID;
tempPos_TransactionType.TransactionTypeName = txtTransactionTypeName.Text;
tempPos_TransactionType.CentralStockFormula = txtCentralStockFormula.Text;
tempPos_TransactionType.ShowRoomFormula = txtShowRoomFormula.Text;
tempPos_TransactionType.ExtraField1 = txtExtraField1.Text;
tempPos_TransactionType.ExtraField2 = txtExtraField2.Text;
tempPos_TransactionType.ExtraField3 = txtExtraField3.Text;
bool result = Pos_TransactionTypeManager.UpdatePos_TransactionType(tempPos_TransactionType);
Response.Redirect("AdminPos_TransactionTypeDisplay.aspx");
}
private void initialLoad()
{
txtDate.Text = DateTime.Today.ToString("dd MMM yyyy");
int transactionTypeID = int.Parse(Request.QueryString["TransactionTypeID"]);
if (transactionTypeID == 10)
{
ddlWorkStation.SelectedValue = "1";
ddlWorkStation.Enabled = false;
}
switch (transactionTypeID)
{
case 10: //Issue Return
ddlWorkStation.SelectedValue = "1";
ddlWorkStation.Enabled = false;
break;
case 23: //Adjustment +
ddlWorkStation.Enabled = false;
break;
case 24: //Adjustment +
ddlWorkStation.Enabled = false;
break;
default:
break;
}
try
{
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(transactionTypeID).TransactionTypeName;
}
catch (Exception ex)
{
lblVoucherType.Text = "Product Search";
}
}
private void loadData()
{
int TransactionTypeID = 9;
string fromDate = (Request.QueryString["FromDate"] != null ? Request.QueryString["FromDate"].ToString() : "");
string toDate = (Request.QueryString["ToDate"] != null ? Request.QueryString["ToDate"].ToString() : "");
lblStockDate.Text = DateTime.Parse(fromDate).ToString("dd/MM/yyyy") + " to " + DateTime.Parse(toDate).ToString("dd/MM/yyyy");
//purchase info
string sql = @"SELECT ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text AS WorkSatation
, ACC_ChartOfAccountLabel4_1.ChartOfAccountLabel4Text AS Product,
Pos_TransactionMaster.TransactionDate, Pos_Product.BarCode,Pos_Product.ProductID,
Pos_TransactionMaster.TransactionID, Pos_Size.SizeName
,Pos_Product.SalePrice as UnitPrice,Pos_Transaction.Quantity
,(Pos_Product.SalePrice * Pos_Transaction.Quantity) as TotalAmount
FROM Pos_Transaction INNER JOIN
Pos_Product ON Pos_Transaction.Pos_ProductID = Pos_Product.Pos_ProductID INNER JOIN
ACC_ChartOfAccountLabel4 AS ACC_ChartOfAccountLabel4_1 ON
Pos_Product.ProductID = ACC_ChartOfAccountLabel4_1.ACC_ChartOfAccountLabel4ID INNER JOIN
ACC_ChartOfAccountLabel4 ON Pos_Transaction.WorkStationID = ACC_ChartOfAccountLabel4.ACC_ChartOfAccountLabel4ID INNER JOIN
Pos_TransactionMaster ON Pos_Transaction.Pos_ProductTransactionMasterID = Pos_TransactionMaster.Pos_TransactionMasterID INNER JOIN
Pos_Size ON Pos_Product.Pos_SizeID = Pos_Size.Pos_SizeID
where Pos_TransactionMaster.Pos_TransactionTypeID=9
and (Pos_TransactionMaster.TransactionDate between '" + fromDate + "' and '" + toDate + "')";
if (Request.QueryString["WorkStationID"] != null)
{
sql += @"
and Pos_Transaction.WorkStationID=" + Request.QueryString["WorkStationID"] + @"
";
}
sql += @"
order by ACC_ChartOfAccountLabel4_1.ChartOfAccountLabel4Text
,Pos_TransactionMaster.TransactionDate,
ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text,Pos_Product.BarCode;";
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(TransactionTypeID).TransactionTypeName;
DataSet ds = CommonManager.SQLExec(sql);
string ProductID = "0";
decimal Total = 0;
decimal Subtotal = 0;
decimal TotalAmount = 0;
decimal SubtotalAmount = 0;
int serialNo = 1;
string htmlTable = @" <table id='itemList_tbl' style='border:1px solid black;width:100%;' cellpadding='0' cellspacing='0'>
";
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (ProductID != "0" && ProductID != dr["ProductID"].ToString())
{
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Subtotal.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.00") + @"</td>
</tr>";
Subtotal = 0;
SubtotalAmount = 0;
}
if (ProductID != dr["ProductID"].ToString())
{
ProductID = dr["ProductID"].ToString();
htmlTable += @"<tr>
<td colspan='10' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;'>
<b>Item:</b> " + dr["Product"].ToString() + @"
</td>
</tr>
<tr id='tableHeader'>
<td style='border-left:0px;'>S/L</td>
<td>Date</td>
<td>Branch Name</td>
<td>Issue ID</td>
<td>Barcode</td>
<td>Product</td>
<td>Size</td>
<td>Qty</td>
<td>Price</td>
<td>Amount</td>
</tr>";
}
htmlTable += @"<tr class='itemCss'>
<td style='border-left:0px;'>" + (serialNo++).ToString() + @"</td>
<td>" + DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy") + @"</td>
<td>" + dr["WorkSatation"].ToString() + @"</td>
<td>" + dr["TransactionID"].ToString() + @"</td>
<td>" + dr["BarCode"].ToString() + @"</td>
<td>" + dr["Product"].ToString() + @"</td>
<td>" + dr["SizeName"].ToString() + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["Quantity"].ToString()).ToString("0,0.00") + @"</td>
<td >" + decimal.Parse(dr["UnitPrice"].ToString()).ToString("0,0.00") + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["TotalAmount"].ToString()).ToString("0,0.00") + @"</td>
</tr>";
Subtotal += decimal.Parse(dr["Quantity"].ToString());
SubtotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
Total += decimal.Parse(dr["Quantity"].ToString());
TotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
}
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Subtotal.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.00") + @"</td>
</tr>";
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>Grand Total</td>
<td>" + Total.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + TotalAmount.ToString("0,0.00") + @"</td>
</tr></table>";
lblItemList.Text = htmlTable;
}
private void loadData()
{
int suppliyerID = 0;
try
{
suppliyerID = Int32.Parse(Request.QueryString["SuppliyerID"]);
}
catch (Exception ex)
{
suppliyerID = 0;
}
int VAT = 0;
try
{
VAT = Int32.Parse(Request.QueryString["VAT"]);
}
catch (Exception ex)
{
VAT = 0;
}
int WorkStationID = 0;
try
{
WorkStationID = Int32.Parse(Request.QueryString["WorkStationID"]);
}
catch (Exception ex)
{
WorkStationID = 0;
}
int TransactionTypeID = 0;
try
{
TransactionTypeID = Int32.Parse(Request.QueryString["TransactionTypeID"]);
}
catch (Exception ex)
{
TransactionTypeID = 0;
}
string fromDate = (Request.QueryString["FromDate"] != null ? Request.QueryString["FromDate"].ToString() : "");
string toDate = (Request.QueryString["ToDate"] != null ? Request.QueryString["ToDate"].ToString() : "");
lblStockDate.Text = DateTime.Parse(fromDate).ToString("dd/MM/yyyy") + " to " + DateTime.Parse(toDate).ToString("dd/MM/yyyy");
//purchase info
string sql = @"select SUM(Pos_Transaction.Quantity) as Quantity
,Inv_QuantityUnit.QuantityUnitName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
,ACC_ChartOfAccountLabel4.ExtraField1
,SUM(Pos_Transaction.Quantity * Pos_Product.SalePrice) as TotalAmount
,SUM((Pos_Transaction.Quantity * Pos_Product.SalePrice)+(Pos_Transaction.Quantity * Pos_Product.SalePrice * [Pos_Product].[VatPercentage] /100)) as TotalAmountWithVAT
,(SUM(Pos_Transaction.Quantity * Pos_Product.SalePrice)/SUM(Pos_Transaction.Quantity)) as UnitPrice
--,Pos_Product.BarCode,Pos_Product.ProductID
from
Pos_Product
inner join ACC_ChartOfAccountLabel4 on ACC_ChartOfAccountLabel4.ACC_ChartOfAccountLabel4ID= Pos_Product.ProductID
inner join Inv_QuantityUnit on Inv_QuantityUnit.Inv_QuantityUnitID=Pos_Product.Inv_QuantityUnitID
inner join Pos_Transaction on Pos_Transaction.Pos_ProductID =Pos_Product.Pos_ProductID
inner join Pos_TransactionMaster on Pos_TransactionMaster.Pos_TransactionMasterID = Pos_Transaction.Pos_ProductTransactionMasterID
where Pos_TransactionMaster.Pos_TransactionTypeID = " + TransactionTypeID.ToString() + @" and (Pos_TransactionMaster.TransactionDate between '" + fromDate + "' and '" + toDate + "')";
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(TransactionTypeID).TransactionTypeName;
if (suppliyerID != 0)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + suppliyerID;
}
if (VAT != 0)
{
if (VAT == 1)
{
sql += " and Pos_Product.VatPercentage =0";//inculding
}
else if (VAT == 2)
{
sql += " and Pos_Product.VatPercentage <> 0";//inculding
}
}
if (WorkStationID != 0)
{
if (
TransactionTypeID == 9
//||
//TransactionTypeID==12
)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + WorkStationID;
}
else
{
sql += " and Pos_TransactionMaster.WorkSatationID =" + WorkStationID;
}
}
sql += @" group by Inv_QuantityUnit.QuantityUnitName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
,ACC_ChartOfAccountLabel4.ExtraField1
order by ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text;";
if (suppliyerID != 0)
{
sql += "select ChartOfAccountLabel4Text,ACC_ChartOfAccountLabel4ID,ExtraField2 from ACC_ChartOfAccountLabel4 where ACC_ChartOfAccountLabel4ID =" + suppliyerID;
}
else
if (WorkStationID != 0)
{
sql += "select ChartOfAccountLabel4Text,ACC_ChartOfAccountLabel4ID,ExtraField2 from ACC_ChartOfAccountLabel4 where ACC_ChartOfAccountLabel4ID =" + WorkStationID;
}
DataSet ds = CommonManager.SQLExec(sql);
string lastSupplierID = "0";
decimal Total = 0;
decimal Subtotal = 0;
decimal TotalAmount = 0;
decimal SubtotalAmount = 0;
int serialNo = 1;
string htmlTable = @" <table id='itemList_tbl' style='border:1px solid black;width:100%;' cellpadding='0' cellspacing='0'>";
if (suppliyerID != 0)
{
htmlTable += @"<tr>
<td colspan='5' style='padding-left:50px;border-top:1px solid black;'>
<b>Supplier name:</b> " + ds.Tables[1].Rows[0]["ChartOfAccountLabel4Text"].ToString() + @"
</td>
<td colspan='2' style=' border-top:1px solid black;'>
<b>Supplier ID:</b> " + ds.Tables[1].Rows[0]["ACC_ChartOfAccountLabel4ID"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='8' style='padding-left:50px;'>
<b>Supplier Address:</b> " + ds.Tables[1].Rows[0]["ExtraField2"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='8' style='padding-left:50px; border-bottom:1px solid black;'>
<b>Transaction Date:</b> " + DateTime.Parse(fromDate).ToString("dd MMM yyyy") + " To " + DateTime.Parse(toDate).ToString("dd MMM yyyy") + @"
</td>
</tr>";
}
if (WorkStationID != 0)
{
htmlTable += @"<tr>
<td colspan='5' style='padding-left:50px;border-top:1px solid black;'>
<b>WorkStation name:</b> " + ds.Tables[1].Rows[0]["ChartOfAccountLabel4Text"].ToString() + @"
</td>
<td colspan='2' style=' border-top:1px solid black;'>
<b>ID:</b> " + ds.Tables[1].Rows[0]["ACC_ChartOfAccountLabel4ID"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='8' style='padding-left:50px;'>
<b>Address:</b> " + ds.Tables[1].Rows[0]["ExtraField2"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='8' style='padding-left:50px; border-bottom:1px solid black;'>
<b>Transaction Date:</b> " + DateTime.Parse(fromDate).ToString("dd MMM yyyy") + " To " + DateTime.Parse(toDate).ToString("dd MMM yyyy") + @"
</td>
</tr>";
}
else
{
htmlTable += @"<tr>
<td colspan='8' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;'>
All WorkStation
</td>
</tr>";
}
htmlTable += @"<tr id='tableHeader'>
<td style='border-left:0px;'>S/L</td>
<td>Item Code</td>
<td>Item Name</td>
<td>Quantity</td>
<td>Unit Price(agv)</td>
<td>Amount(excl VAT)</td>
<td>Amount(incl VAT)</td>
</tr>
";
foreach (DataRow dr in ds.Tables[0].Rows)
{
htmlTable += @"<tr class='itemCss'>
<td style='border-left:0px;'>" + (serialNo++).ToString() + @"</td>
<td>" + dr["ExtraField1"].ToString() + @"</td>
<td>" + dr["ChartOfAccountLabel4Text"].ToString() + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["Quantity"].ToString()).ToString("0,0.00") + @"</td>
<td >" + decimal.Parse(dr["UnitPrice"].ToString()).ToString("0,0.00") + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["TotalAmount"].ToString()).ToString("0,0.00") + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["TotalAmountWithVAT"].ToString()).ToString("0,0.00") + @"</td>
</tr>";
Subtotal += decimal.Parse(dr["Quantity"].ToString());
SubtotalAmount += decimal.Parse(dr["TotalAmountWithVAT"].ToString());
//SubtotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
Total += decimal.Parse(dr["Quantity"].ToString());
TotalAmount += decimal.Parse(dr["TotalAmountWithVAT"].ToString());
//TotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
}
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td>Total</td>
<td>" + Total.ToString("0,0.00") + @"</td>
<td> </td>
<td> </td>
<td>" + TotalAmount.ToString("0,0.00") + @"</td>
</tr></table>";
lblItemList.Text = htmlTable;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (ddlWorkStation.SelectedValue == "0")
{
showAlartMessage("Please select the Work Station");
return;
}
if (Request.QueryString["TransactionTypeID"] == "15")
{
ddlWorkStation.SelectedValue = "1";
if (ddlRefference.SelectedValue == "0")
{
showAlartMessage("Please select the Work Station or Ref");
return;
}
}
if (gvPos_Product.Rows.Count == 0)
{
showAlartMessage("Plz add some product");
return;
}
foreach (GridViewRow gvr in gvPos_Product.Rows)
{
Label lblBarCode = (Label)gvr.FindControl("lblBarCode");
TextBox txtQty = (TextBox)gvr.FindControl("txtQty");
Label lblStock = (Label)gvr.FindControl("lblStock");
if (decimal.Parse(lblStock.Text) < decimal.Parse(txtQty.Text))
{
showAlartMessage(lblBarCode.Text + " is going to issue more than stock. pleae enter the correct value");
return;
}
}
btnSubmit.Visible = false;
int transactionTypeID = int.Parse(Request.QueryString["TransactionTypeID"]);
int TransactionMasterID = 0;
if (txtOldTransactionID.Text != "")
{
if (txtOldTransactionID.Text != "0")
{
TransactionMasterID = int.Parse(GetTransactionMasterID());
}
else
{
TransactionMasterID = int.Parse(txtOldTransactionID.Text);
}
if (TransactionMasterID == 0)
{
showAlartMessage("Wrong old ID");
return;
}
}
else
{
Pos_TransactionMaster pos_TransactionMaster = new Pos_TransactionMaster();
pos_TransactionMaster.TransactionDate = DateTime.Parse(txtDate.Text);
pos_TransactionMaster.TransactionID = 0;
pos_TransactionMaster.Pos_TransactionTypeID = transactionTypeID;
pos_TransactionMaster.ToOrFromID = int.Parse(ddlWorkStation.SelectedValue);
pos_TransactionMaster.Record = "";
pos_TransactionMaster.Particulars = "";
pos_TransactionMaster.WorkSatationID = 1;
pos_TransactionMaster.ExtraField1 = "";
pos_TransactionMaster.ExtraField2 = "";
pos_TransactionMaster.ExtraField3 = "";
pos_TransactionMaster.ExtraField4 = "";
pos_TransactionMaster.ExtraField5 = (transactionTypeID == 9? "Pending":"");
pos_TransactionMaster.AddedBy = getLogin().LoginID;
pos_TransactionMaster.AddedDate = DateTime.Now;
pos_TransactionMaster.UpdatedBy = getLogin().LoginID;
pos_TransactionMaster.UpdatedDate = DateTime.Now;
pos_TransactionMaster.RowStatusID = 1;
TransactionMasterID = Pos_TransactionMasterManager.InsertPos_TransactionMaster(pos_TransactionMaster);
pos_TransactionMaster = Pos_TransactionMasterManager.GetPos_TransactionMasterByID(TransactionMasterID);
txtOldTransactionID.Text = pos_TransactionMaster.TransactionID.ToString();
}
string sql = "Declare @Count int; ";
foreach (GridViewRow gvr in gvPos_Product.Rows)
{
HiddenField hfPos_ProductID = (HiddenField)gvr.FindControl("hfPos_ProductID");
TextBox txtQty = (TextBox)gvr.FindControl("txtQty");
Label lblStock = (Label)gvr.FindControl("lblStock");
if (txtQty.Text == "")
{
continue;
}
if (decimal.Parse(txtQty.Text) == 0)
{
continue;
}
if (decimal.Parse(lblStock.Text) < decimal.Parse(txtQty.Text))
{
txtQty.Text = lblStock.Text;
}
Pos_Transaction pos_Transaction = new Pos_Transaction();
pos_Transaction.Pos_ProductID = Int32.Parse(hfPos_ProductID.Value);
pos_Transaction.Quantity = Decimal.Parse(txtQty.Text);
pos_Transaction.Pos_ProductTrasactionTypeID = transactionTypeID;
pos_Transaction.Pos_ProductTransactionMasterID = TransactionMasterID;
pos_Transaction.WorkStationID = Int32.Parse(ddlWorkStation.SelectedValue);
pos_Transaction.ExtraField1 = "";
pos_Transaction.ExtraField2 = "";
pos_Transaction.ExtraField3 = "";
pos_Transaction.ExtraField4 = "";
pos_Transaction.ExtraField5 = "";
pos_Transaction.AddedBy = getLogin().LoginID;
pos_Transaction.AddedDate = DateTime.Now;
pos_Transaction.UpdatedBy = getLogin().LoginID;
pos_Transaction.UpdatedDate = DateTime.Now;
pos_Transaction.RowStatusID = 1;
int resutl = Pos_TransactionManager.InsertPos_Transaction(pos_Transaction);
bool IsGeneralOperation = false;
//accounts need to update
switch (transactionTypeID)
{
case 9: //Issue
IsGeneralOperation = true;
break;
case 5: //Adjustment -
IsGeneralOperation = true;
break;
case 6: //Adjustment +
IsGeneralOperation = true;
break;
default:
break;
}
if (IsGeneralOperation)
{
Pos_TransactionType transactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(transactionTypeID);
if (transactionType.CentralStockFormula != "0")
{
sql += "Update Pos_Product set ExtraField1 =(cast(ExtraField1 as decimal(10,2)) + ((" + transactionType.CentralStockFormula + ")*" + txtQty.Text + ")) where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + ";";
}
/*
* Any thing will be send to another place need the accepting from the receiver
* if (transactionType.ShowRoomFormula != "0")
* {
* sql += @"
* set @Count=
* (
* select COUNT(*) from Pos_WorkStationStock
* where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @" and WorkStationID="+ddlWorkStation.SelectedValue+@"
* )
*
* if @Count = 0
* BEGIN
* INSERT INTO [Pos_WorkStationStock]
* ([WorkStationID]
* ,[ProductID]
* ,[Stock])
* VALUES(" + ddlWorkStation.SelectedValue + @"," + pos_Transaction.Pos_ProductID.ToString() + @"," + txtQty.Text + @");
* END
* ELSE
* BEGIN
* Update Pos_WorkStationStock set Stock += ((" + transactionType.ShowRoomFormula + @")*" + txtQty.Text + @") where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
* END;";
* }
*/
}
}
saveForRemoteDatabase(TransactionMasterID);
CommonManager.SQLExec(sql);
btnSearch_Click(this, new EventArgs());
hlnkProductionPrint.Visible = true;
hlnkProductionPrint.NavigateUrl = (Request.QueryString["TransactionTypeID"] == "9" ? "DelivaryChalanPrint" : "TransactionPrint") + ".aspx?Pos_TransactionMasterID=" + TransactionMasterID.ToString();
}
protected void btnReceived_Click(object sender, EventArgs e)
{
int Pos_TransactionMasterID = int.Parse(Request.QueryString["Pos_TransactionMasterID"]);
Pos_TransactionMaster pos_TransactionMaster = Pos_TransactionMasterManager.GetPos_TransactionMasterByID(Pos_TransactionMasterID);
Pos_TransactionType transactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(pos_TransactionMaster.Pos_TransactionTypeID);
string sql = "Declare @Count int; ";
sql += "update Pos_TransactionMaster set ExtraField5='' where Pos_TransactionMasterID=" + Pos_TransactionMasterID + ";";
List <Pos_Product> items = new List <Pos_Product>();
items = Pos_ProductManager.GetAllPos_ProductsByTrasactionMasterID(Pos_TransactionMasterID);
//updated by is the transaction ID
string accountingEntry = "";
accountingEntry += @"
Declare @JournalMasterID int
Declare @ProductID int
Declare @FinishedGoodsAmount decimal(18,2)
INSERT INTO [ACC_JournalMaster]
([JournalMasterName]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[Note]
,[JournalDate]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
('1'--<JournalMasterName, nvarchar(50),>
,''--<ExtraField1, nvarchar(256),>
,'POS ISSUE'--<ExtraField2, nvarchar(256),>
,'" + Pos_TransactionMasterID.ToString() + @"'--<ExtraField3, nvarchar(256),>
,'" + pos_TransactionMaster.ToOrFromName + " " + "Chalan #: " + pos_TransactionMaster.TransactionID + @"'--<Note, nvarchar(256),>
,GETDATE()--<JournalDate, datetime,>
," + pos_TransactionMaster.AddedBy.ToString() + @"--<AddedBy, int,>
,GETDATE()--<AddedDate, datetime,>
," + pos_TransactionMaster.AddedBy.ToString() + @"--<UpdatedBy, int,>
,GETDATE()--<UpdatedDate, datetime,>
,1--<RowStatusID, int,>
);
Set @JournalMasterID = SCOPE_IDENTITY();
";
foreach (Pos_Product pos_Transaction in items)
{
//Finished Good(Aseet) -- branch
accountingEntry += @"
Set @ProductID=(Select ProductID from Pos_Product where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @")
set @FinishedGoodsAmount=(select ([Pos_Product].[FabricsCost] + [Pos_Product].[AccesoriesCost] + [Pos_Product].[Overhead]+[Pos_Product].[OthersCost]+[Pos_Product].[PurchasePrice]) from Pos_Product where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @")
INSERT INTO [ACC_JournalDetail]
([JournalMasterID]
,[ACC_ChartOfAccountLabel4ID]
,[ACC_ChartOfAccountLabel3ID]
,[WorkStation]
,[Debit]
,[Credit]
,[ExtraField3]
,[ExtraField2]
,[ExtraField1]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@JournalMasterID--<JournalMasterID, int,>
,@ProductID--<ACC_ChartOfAccountLabel4ID, int,>
,9--<ACC_ChartOfAccountLabel3ID, int,>
," + pos_TransactionMaster.ToOrFromID + @"--<WorkStation, int,>
,(@FinishedGoodsAmount *" + decimal.Parse(pos_Transaction.ExtraField1).ToString("0.00") + @")--<Debit, decimal(20,2),>
,0--<Credit, decimal(20,2),>
,'" + pos_Transaction.UpdatedBy + @"'--<ExtraField3, nvarchar(256),>
,'POS ISSUE'--<ExtraField2, nvarchar(256),>
,''--<ExtraField1, nvarchar(256),>
," + pos_TransactionMaster.AddedBy.ToString() + @"--<AddedBy, int,>
,GETDATE()--<AddedDate, datetime,>
," + pos_TransactionMaster.AddedBy.ToString() + @"--<UpdatedBy, int,>
,GETDATE()--<UpdatedDate, datetime,>
,1--<RowStatusID, int,>
) ;
";
//Finished Good(Aseet) -- head office
accountingEntry += @"
Set @ProductID=(Select ProductID from Pos_Product where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @")
set @FinishedGoodsAmount=(select ([Pos_Product].[FabricsCost] + [Pos_Product].[AccesoriesCost] + [Pos_Product].[Overhead]+[Pos_Product].[OthersCost]+[Pos_Product].[PurchasePrice]) from Pos_Product where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @")
INSERT INTO [ACC_JournalDetail]
([JournalMasterID]
,[ACC_ChartOfAccountLabel4ID]
,[ACC_ChartOfAccountLabel3ID]
,[WorkStation]
,[Debit]
,[Credit]
,[ExtraField3]
,[ExtraField2]
,[ExtraField1]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@JournalMasterID--<JournalMasterID, int,>
,@ProductID--<ACC_ChartOfAccountLabel4ID, int,>
,9--<ACC_ChartOfAccountLabel3ID, int,>
,1--<WorkStation, int,>
,0--<Debit, decimal(20,2),>
,(@FinishedGoodsAmount *" + decimal.Parse(pos_Transaction.ExtraField1).ToString("0.00") + @")--<Credit, decimal(20,2),>
,'" + pos_Transaction.UpdatedBy + @"'--<ExtraField3, nvarchar(256),>
,'POS ISSUE'--<ExtraField2, nvarchar(256),>
,''--<ExtraField1, nvarchar(256),>
," + pos_TransactionMaster.AddedBy.ToString() + @"--<AddedBy, int,>
,GETDATE()--<AddedDate, datetime,>
," + pos_TransactionMaster.AddedBy.ToString() + @"--<UpdatedBy, int,>
,GETDATE()--<UpdatedDate, datetime,>
,1--<RowStatusID, int,>
) ;
";
if (pos_TransactionMaster.ToOrFromID == 1 && transactionType.CentralStockFormula != "0")
{
//For head office Central Stock
sql += "Update Pos_Product set ExtraField1 =(cast(ExtraField1 as decimal(10,2)) + ((" + transactionType.CentralStockFormula + ")*" + pos_Transaction.ExtraField1 + ")) where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + ";";
}
else
if (pos_TransactionMaster.ToOrFromID != 1 && transactionType.ShowRoomFormula != "0")
{
sql += @"
set @Count=
(
select COUNT(*) from Pos_WorkStationStock
where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @" and WorkStationID=" + pos_TransactionMaster.ToOrFromID + @"
)
if @Count = 0
BEGIN
INSERT INTO [Pos_WorkStationStock]
([WorkStationID]
,[ProductID]
,[Stock])
VALUES(" + pos_TransactionMaster.ToOrFromID + @"," + pos_Transaction.Pos_ProductID.ToString() + @"," + pos_Transaction.ExtraField1 + @");
END
ELSE
BEGIN
Update Pos_WorkStationStock set Stock += ((+1)*" + pos_Transaction.ExtraField1 + @") where WorkStationID=" + pos_TransactionMaster.ToOrFromID + @" and ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
END;
";
}
}
//CommonManager.SQLExec(sql);
CommonManager.SQLExec(sql + accountingEntry);
Response.Redirect("DelivaryChalanPrint.aspx?Pos_TransactionMasterID=" + Pos_TransactionMasterID);
}
private void loadData()
{
int Pos_TransactionMasterID = int.Parse(Request.QueryString["Pos_TransactionMasterID"]);
Pos_TransactionMaster pos_TransactionMaster = Pos_TransactionMasterManager.GetPos_TransactionMasterByID(Pos_TransactionMasterID);
lblPurchaseDate.Text = pos_TransactionMaster.TransactionDate.ToString("dd-MMM-yyyy");
lblParticulars.Text = pos_TransactionMaster.Particulars;
lblTransactionID.Text = pos_TransactionMaster.TransactionID.ToString() + (pos_TransactionMaster.RowStatusID == 1? (Request.QueryString["Delete"] != null ? " (<a href='TransactionDelete.aspx?Pos_TransactionMasterID=" + Request.QueryString["Pos_TransactionMasterID"] + "'>X</a>)" : ""):"<b style='color:red;'>(Deleted)</b>");
lblRefferenceIDs.Text = pos_TransactionMaster.Record;
lblToOrFromName.Text = pos_TransactionMaster.ToOrFromName;
if ((pos_TransactionMaster.ToOrFromID.ToString() != getLogin().ExtraField5
&&
pos_TransactionMaster.WorkSatationID.ToString() != getLogin().ExtraField5
) && getLogin().ExtraField5 != "1")
{
showAlartMessage("Your are not allowed to View this Transaction");
btnReceived.Visible = false;
return;
}
if (pos_TransactionMaster.ExtraField5 == "Pending")
{
lblExtraField5.Text = pos_TransactionMaster.ExtraField5;
if (pos_TransactionMaster.Pos_TransactionTypeID == 12
&&
getLogin().ExtraField5 == pos_TransactionMaster.WorkSatationID.ToString()
)
{
lblExtraField5.Visible = true;
btnReceived.Visible = true;
}
else
if (getLogin().ExtraField5 == pos_TransactionMaster.ToOrFromID.ToString())
{
lblExtraField5.Visible = true;
btnReceived.Visible = true;
}
else
{
lblExtraField5.Visible = false;
btnReceived.Visible = false;
}
}
else
{
lblExtraField5.Visible = false;
btnReceived.Visible = false;
}
string NoOfcollum = "9";
trRefference.Visible = false;
trToOrFrom.Visible = false;
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(pos_TransactionMaster.Pos_TransactionTypeID).TransactionTypeName;
switch (pos_TransactionMaster.Pos_TransactionTypeID)
{
case 1:
break;
case 2:
break;
case 3:
break;
case 4:
break;
case 5:
break;
case 6:
break;
case 7:
break;
case 8:
break;
case 9:
trToOrFrom.Visible = true;
break;
case 10:
trToOrFrom.Visible = true;
break;
case 11: //Branch Transfer
trToOrFrom.Visible = true;
break;
case 12: //Branch Transfer
trToOrFrom.Visible = true;
break;
case 25:
trToOrFrom.Visible = true;
NoOfcollum = "10";
break;
default:
break;
}
//Item Info
List <Pos_Product> items = new List <Pos_Product>();
items = Pos_ProductManager.GetAllPos_ProductsByTrasactionMasterID(Pos_TransactionMasterID);
string htmlTable = @" <table id='itemList_tbl' style='border:1px solid black;width:100%;' cellpadding='0' cellspacing='0'>
<tr id='tableHeader'>
<td style='border-left:0px;'>S/L</td>
<td>Product Code</td>
<td>Product Name</td>
<td>Status</td>
<td>Quantity</td>
<td>Unit</td>
" + (NoOfcollum == "10" ? "<td>Cost</td><td>Total Cost</td>" : "")
+ @"<td>Sale Price</td>
<td>VAT %</td>
<td>Amount</td>
</tr>";
int lastRawMaterialID = 0;
decimal Total = 0;
decimal Subtotal = 0;
decimal TotalAmount = 0;
decimal SubtotalAmount = 0;
decimal TotalAmountCost = 0;
decimal SubtotalAmountCost = 0;
int serialNo = 1;
foreach (Pos_Product item in items)
{
if (item.RowStatusID != 1)
{
continue;
}
if (lastRawMaterialID != 0 && lastRawMaterialID != item.ProductID)
{
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Subtotal.ToString("0,0.000000") + @"</td>
<td> </td>
" + (NoOfcollum == "10" ? "<td> </td><td>" + SubtotalAmountCost.ToString("0,0.000000") + @"</td>" : "")
+ @"<td> </td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.000000") + @"</td>
</tr>";
Subtotal = 0;
SubtotalAmount = 0;
SubtotalAmountCost = 0;
}
if (lastRawMaterialID != item.ProductID)
{
lastRawMaterialID = item.ProductID;
htmlTable += @"<tr>
<td colspan='" + NoOfcollum + @"' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;font-weight:bold;'>
Item: " + item.ExtraField2 + @"
</td>
</tr>";
}
htmlTable += @"<tr class='itemCss'>
<td style='border-left:0px;'>" + (serialNo++).ToString() + (item.RowStatusID == 1 ? (Request.QueryString["Delete"] != null ? (pos_TransactionMaster.Pos_TransactionTypeID == 13?"": " (<a href='TransactionDelete.aspx?Pos_TransactionID=" + item.ExtraField10 + "&Pos_TransactionMasterID=" + Request.QueryString["Pos_TransactionMasterID"] + "'>X</a>)") : "") : "<b style='color:red;'>(Deleted)</b>") + @"</td>
<td>" + item.BarCode + @"</td>
<td>" + item.ExtraField2 + @"</td>
<td>" + item.ExtraField8 + @"</td>
<td style='text-align:right;'>" + decimal.Parse(item.ExtraField1).ToString("0,0.000000") + @"</td>
<td >" + item.ExtraField3 + @"</td>
" + (NoOfcollum == "10" ? "<td style='text-align:right;'>" + decimal.Parse(item.ExtraField4).ToString("0,0.000000") + @"</td><td>" + (decimal.Parse(item.ExtraField1) * decimal.Parse(item.ExtraField4)).ToString("0,0.000000") + @"</td>" : "")
+ @"<td style='text-align:right;'>" + item.SalePrice.ToString("0,0.000000") + @"</td>
<td style='text-align:right;'>" + item.VatPercentage.ToString("0,0.000000") + @"</td>
<td style='text-align:right;'>" + ((decimal.Parse(item.ExtraField1) * item.SalePrice) + ((decimal.Parse(item.ExtraField1) * item.SalePrice) * item.VatPercentage / 100)).ToString("0,0.000000") + @"</td>
</tr>";
Subtotal += decimal.Parse(item.ExtraField1);
SubtotalAmount += ((decimal.Parse(item.ExtraField1) * item.SalePrice) + ((decimal.Parse(item.ExtraField1) * item.SalePrice) * item.VatPercentage / 100));
SubtotalAmountCost += decimal.Parse(item.ExtraField1) * decimal.Parse(item.ExtraField4);
Total += decimal.Parse(item.ExtraField1);
TotalAmount += ((decimal.Parse(item.ExtraField1) * item.SalePrice) + ((decimal.Parse(item.ExtraField1) * item.SalePrice) * item.VatPercentage / 100));
TotalAmountCost += decimal.Parse(item.ExtraField1) * decimal.Parse(item.ExtraField4);
}
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Subtotal.ToString("0,0.000000") + @"</td>
<td> </td>
" + (NoOfcollum == "10" ? "<td> </td><td>" + SubtotalAmountCost.ToString("0,0.000000") + @"</td>" : "")
+ @"<td> </td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.000000") + @"</td>
</tr>";
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Grand Total</td>
<td>" + Total.ToString("0,0.000000") + @"</td>
<td> </td>
" + (NoOfcollum == "10" ? "<td> </td><td>" + TotalAmountCost.ToString("0,0.000000") + @"</td>" : "")
+ @"<td> </td>
<td> </td>
<td>" + TotalAmount.ToString("0,0.000000") + @"</td>
</tr></table>";
lblItemList.Text = htmlTable;
}
private void showPos_TransactionTypeGrid()
{
gvPos_TransactionType.DataSource = Pos_TransactionTypeManager.GetAllPos_TransactionTypes();
gvPos_TransactionType.DataBind();
}
private void loadData()
{
int suppliyerID = 0;
try
{
suppliyerID = Int32.Parse(Request.QueryString["SuppliyerID"]);
}
catch (Exception ex)
{
suppliyerID = 0;
}
int WorkStationID = 0;
try
{
WorkStationID = Int32.Parse(Request.QueryString["WorkStationID"]);
}
catch (Exception ex)
{
WorkStationID = 0;
}
int TransactionTypeID = 0;
try
{
TransactionTypeID = Int32.Parse(Request.QueryString["TransactionTypeID"]);
}
catch (Exception ex)
{
TransactionTypeID = 0;
}
string fromDate = (Request.QueryString["FromDate"] != null ? Request.QueryString["FromDate"].ToString() : "");
string toDate = (Request.QueryString["ToDate"] != null ? Request.QueryString["ToDate"].ToString() : "");
lblStockDate.Text = DateTime.Parse(fromDate).ToString("dd/MM/yyyy") + " to " + DateTime.Parse(toDate).ToString("dd/MM/yyyy");
//purchase info
string sql = @"select SUM(Pos_Transaction.Quantity) as Quantity
,Inv_QuantityUnit.QuantityUnitName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
,ACC_ChartOfAccountLabel4.ExtraField1
,SUM(Pos_Transaction.Quantity * Pos_Product.SalePrice) as TotalAmount
,(SUM(Pos_Transaction.Quantity * Pos_Product.SalePrice)/SUM(Pos_Transaction.Quantity)) as UnitPrice
,Pos_Product.BarCode,Pos_Product.ProductID
,Pos_TransactionMaster.TransactionDate
from
Pos_Product
inner join ACC_ChartOfAccountLabel4 on ACC_ChartOfAccountLabel4.ACC_ChartOfAccountLabel4ID= Pos_Product.ProductID
inner join Inv_QuantityUnit on Inv_QuantityUnit.Inv_QuantityUnitID=Pos_Product.Inv_QuantityUnitID
inner join Pos_Transaction on Pos_Transaction.Pos_ProductID =Pos_Product.Pos_ProductID
inner join Pos_TransactionMaster on Pos_TransactionMaster.Pos_TransactionMasterID = Pos_Transaction.Pos_ProductTransactionMasterID
where Pos_TransactionMaster.RowStatusID=1 and Pos_TransactionMaster.Pos_TransactionTypeID =" + TransactionTypeID.ToString() + @" and (Pos_TransactionMaster.TransactionDate between '" + fromDate + "' and '" + toDate + "')";
if (suppliyerID != 0)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + suppliyerID;
}
if (WorkStationID != 0)
{
if (
TransactionTypeID == 9
||
TransactionTypeID == 12
)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + WorkStationID;
}
else
{
sql += " and Pos_TransactionMaster.WorkSatationID =" + WorkStationID;
}
}
sql += @"
group by Inv_QuantityUnit.QuantityUnitName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
,ACC_ChartOfAccountLabel4.ExtraField1
,Pos_Product.BarCode,Pos_Product.ProductID
,Pos_TransactionMaster.TransactionDate
order by Pos_TransactionMaster.TransactionDate,Pos_Product.ProductID,Pos_Product.BarCode;";
if (suppliyerID != 0)
{
sql += "select ChartOfAccountLabel4Text,ACC_ChartOfAccountLabel4ID,ExtraField2 from ACC_ChartOfAccountLabel4 where ACC_ChartOfAccountLabel4ID =" + suppliyerID;
}
else
if (WorkStationID != 0)
{
sql += "select ChartOfAccountLabel4Text,ACC_ChartOfAccountLabel4ID,ExtraField2 from ACC_ChartOfAccountLabel4 where ACC_ChartOfAccountLabel4ID =" + WorkStationID;
}
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(TransactionTypeID).TransactionTypeName;
DataSet ds = CommonManager.SQLExec(sql);
string ProductID = "0";
decimal Total = 0;
decimal Subtotal = 0;
decimal TotalAmount = 0;
decimal SubtotalAmount = 0;
decimal grandTotal = 0;
decimal grandTotalAmount = 0;
int serialNo = 1;
string htmlTable = @" <table id='itemList_tbl' style='border:1px solid black;width:100%;' cellpadding='0' cellspacing='0'>
";
if (suppliyerID != 0)
{
htmlTable += @"<tr>
<td colspan='4' style='padding-left:50px;border-top:1px solid black;'>
<b>Supplier name:</b> " + ds.Tables[1].Rows[0]["ChartOfAccountLabel4Text"].ToString() + @"
</td>
<td colspan='2' style=' border-top:1px solid black;'>
<b>Supplier ID:</b> " + ds.Tables[1].Rows[0]["ACC_ChartOfAccountLabel4ID"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='7' style='padding-left:50px;'>
<b>Supplier Address:</b> " + ds.Tables[1].Rows[0]["ExtraField2"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='7' style='padding-left:50px; border-bottom:1px solid black;'>
<b>Transaction Date:</b> " + DateTime.Parse(fromDate).ToString("dd MMM yyyy") + " To " + DateTime.Parse(toDate).ToString("dd MMM yyyy") + @"
</td>
</tr>";
}
if (WorkStationID != 0)
{
htmlTable += @"<tr>
<td colspan='5' style='padding-left:50px;border-top:1px solid black;'>
<b>WorkStation name:</b> " + ds.Tables[1].Rows[0]["ChartOfAccountLabel4Text"].ToString() + @"
</td>
<td colspan='2' style=' border-top:1px solid black;'>
<b>ID:</b> " + ds.Tables[1].Rows[0]["ACC_ChartOfAccountLabel4ID"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='7' style='padding-left:50px;'>
<b>Address:</b> " + ds.Tables[1].Rows[0]["ExtraField2"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='7' style='padding-left:50px; border-bottom:1px solid black;'>
<b>Transaction Date:</b> " + DateTime.Parse(fromDate).ToString("dd MMM yyyy") + " To " + DateTime.Parse(toDate).ToString("dd MMM yyyy") + @"
</td>
</tr>";
}
else
{
htmlTable += @"<tr>
<td colspan='7' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;'>
All
</td>
</tr>";
}
string lastDate = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (lastDate == "")
{
htmlTable += @"<tr>
<td colspan='7' style='background-color:gray;padding-left:20px;font-weight:bold;font-size:15px; border-top:1px solid black;border-bottom:1px solid black;'>
Date: " + DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy") + @"
</td>
</tr>";
Subtotal = 0;
SubtotalAmount = 0;
Total = 0;
TotalAmount = 0;
lastDate = DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy");
}
else
if (lastDate != DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy"))
{
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Total</td>
<td>" + Subtotal.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.00") + @"</td>
</tr>";
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Total.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + TotalAmount.ToString("0,0.00") + @"</td>
</tr>";
Subtotal = 0;
SubtotalAmount = 0;
Total = 0;
TotalAmount = 0;
htmlTable += @"<tr>
<td colspan='7' style='background-color:gray;padding-left:20px;font-weight:bold;font-size:15px; border-top:1px solid black;border-bottom:1px solid black;'>
Date: " + DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy") + @"
</td>
</tr>";
lastDate = DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy");
}
else
if (ProductID != "0" && ProductID != dr["ProductID"].ToString())
{
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Subtotal.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.00") + @"</td>
</tr>";
Subtotal = 0;
SubtotalAmount = 0;
}
if (ProductID != dr["ProductID"].ToString())
{
ProductID = dr["ProductID"].ToString();
htmlTable += @"<tr>
<td colspan='7' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;'>
<b>Item:</b> " + dr["ChartOfAccountLabel4Text"].ToString() + @"
</td>
</tr>
<tr id='tableHeader'>
<td style='border-left:0px;'>S/L</td>
<td>Date</td>
<td>Item Code</td>
<td>Item Name</td>
<td>Quantity</td>
<td>Unit Price</td>
<td>Amount</td>
</tr>";
}
htmlTable += @"<tr class='itemCss'>
<td style='border-left:0px;'>" + (serialNo++).ToString() + @"</td>
<td>" + DateTime.Parse(dr["TransactionDate"].ToString()).ToString("dd MMM yyyy") + @"</td>
<td>" + dr["BarCode"].ToString() + @"</td>
<td>" + dr["ChartOfAccountLabel4Text"].ToString() + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["Quantity"].ToString()).ToString("0,0.00") + @"</td>
<td >" + decimal.Parse(dr["UnitPrice"].ToString()).ToString("0,0.00") + @"</td>
<td style='text-align:right;'>" + decimal.Parse(dr["TotalAmount"].ToString()).ToString("0,0.00") + @"</td>
</tr>";
Subtotal += decimal.Parse(dr["Quantity"].ToString());
SubtotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
Total += decimal.Parse(dr["Quantity"].ToString());
TotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
grandTotal += decimal.Parse(dr["Quantity"].ToString());
grandTotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
}
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Total</td>
<td>" + Subtotal.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + SubtotalAmount.ToString("0,0.00") + @"</td>
</tr>";
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td>" + Total.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + TotalAmount.ToString("0,0.00") + @"</td>
</tr>";
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td> </td>
<td>Grand Total</td>
<td>" + grandTotal.ToString("0,0.00") + @"</td>
<td> </td>
<td>" + grandTotalAmount.ToString("0,0.00") + @"</td>
</tr></table>";
lblItemList.Text = htmlTable;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
int transactionTypeID = int.Parse(Request.QueryString["TransactionTypeID"]);
int TransactionMasterID = 0;
bool IsIssueReturn = false;
bool isTransferToanotherShowroom = false;
bool isAdjustment = false;
string Pending = "Pending";
switch (transactionTypeID)
{
case 10: //Issue Return
IsIssueReturn = true;
break;
case 11: //Transfer to another showroom
isTransferToanotherShowroom = true;
break;
case 23: //Adjustment +
isAdjustment = true;
Pending = "";
break;
case 24: //Adjustment +
isAdjustment = true;
Pending = "";
break;
default:
break;
}
if (txtOldTransactionID.Text != "")
{
if (txtOldTransactionID.Text != "0")
{
TransactionMasterID = int.Parse(GetTransactionMasterID());
}
else
{
TransactionMasterID = int.Parse(txtOldTransactionID.Text);
}
if (TransactionMasterID == 0)
{
showAlartMessage("Wrong old ID");
return;
}
}
else
{
Pos_TransactionMaster pos_TransactionMaster = new Pos_TransactionMaster();
Pos_TransactionMaster pos_TransactionMasterOposite = new Pos_TransactionMaster();
pos_TransactionMaster.TransactionDate = DateTime.Parse(txtDate.Text);
pos_TransactionMaster.TransactionID = 0;
pos_TransactionMaster.Pos_TransactionTypeID = transactionTypeID;
pos_TransactionMaster.ToOrFromID = (isAdjustment?int.Parse(ddlWorkStationSearch.SelectedValue): int.Parse(ddlWorkStation.SelectedValue));
pos_TransactionMaster.Record = "";
pos_TransactionMaster.Particulars = "";
pos_TransactionMaster.WorkSatationID = int.Parse(ddlWorkStationSearch.SelectedValue);
pos_TransactionMaster.ExtraField1 = "";
pos_TransactionMaster.ExtraField2 = "";
pos_TransactionMaster.ExtraField3 = "";
pos_TransactionMaster.ExtraField4 = "";
pos_TransactionMaster.ExtraField5 = Pending;
pos_TransactionMaster.AddedBy = getLogin().LoginID;
pos_TransactionMaster.AddedDate = DateTime.Now;
pos_TransactionMaster.UpdatedBy = getLogin().LoginID;
pos_TransactionMaster.UpdatedDate = DateTime.Now;
pos_TransactionMaster.RowStatusID = 1;
if (isTransferToanotherShowroom)
{
TransactionMasterID = Pos_TransactionMasterManager.InsertPos_TransactionMaster(pos_TransactionMaster, true);
}
else
{
TransactionMasterID = Pos_TransactionMasterManager.InsertPos_TransactionMaster(pos_TransactionMaster);
}
pos_TransactionMaster = Pos_TransactionMasterManager.GetPos_TransactionMasterByID(TransactionMasterID);
txtOldTransactionID.Text = pos_TransactionMaster.TransactionID.ToString();
}
string sql = "Declare @Count int; ";
foreach (GridViewRow gvr in gvPos_Product.Rows)
{
HiddenField hfPos_ProductID = (HiddenField)gvr.FindControl("hfPos_ProductID");
TextBox txtQty = (TextBox)gvr.FindControl("txtQty");
if (txtQty.Text == "")
{
continue;
}
if (decimal.Parse(txtQty.Text) == 0)
{
continue;
}
Pos_Transaction pos_Transaction = new Pos_Transaction();
pos_Transaction.Pos_ProductID = Int32.Parse(hfPos_ProductID.Value);
pos_Transaction.Quantity = Decimal.Parse(txtQty.Text);
pos_Transaction.Pos_ProductTrasactionTypeID = transactionTypeID;
pos_Transaction.Pos_ProductTransactionMasterID = TransactionMasterID;
pos_Transaction.WorkStationID = Int32.Parse(ddlWorkStationSearch.SelectedValue);
pos_Transaction.ExtraField1 = "";
pos_Transaction.ExtraField2 = "";
pos_Transaction.ExtraField3 = "";
pos_Transaction.ExtraField4 = "";
pos_Transaction.ExtraField5 = "";
pos_Transaction.AddedBy = getLogin().LoginID;
pos_Transaction.AddedDate = DateTime.Now;
pos_Transaction.UpdatedBy = getLogin().LoginID;
pos_Transaction.UpdatedDate = DateTime.Now;
pos_Transaction.RowStatusID = 1;
if (isTransferToanotherShowroom)
{
Pos_TransactionManager.InsertPos_TransactionWithOpositeEntry(pos_Transaction);
}
else
{
Pos_TransactionManager.InsertPos_Transaction(pos_Transaction);
}
if (IsIssueReturn)
{
Pos_TransactionType transactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(transactionTypeID);
if (transactionType.CentralStockFormula != "0")
{
//sql += "Update Pos_Product set ExtraField1 =(cast(ExtraField1 as decimal(10,2)) + ((" + transactionType.CentralStockFormula + ")*" + txtQty.Text + ")) where Pos_ProductID=" + pos_Transaction.Pos_ProductID.ToString() + ";";
}
if (transactionType.ShowRoomFormula != "0")
{
sql += @"
set @Count=
(
select COUNT(*) from Pos_WorkStationStock
where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @" and WorkStationID=" + ddlWorkStationSearch.SelectedValue + @"
)
if @Count = 0
BEGIN
INSERT INTO [Pos_WorkStationStock]
([WorkStationID]
,[ProductID]
,[Stock])
VALUES(" + ddlWorkStationSearch.SelectedValue + @"," + pos_Transaction.Pos_ProductID.ToString() + @"," + txtQty.Text + @");
END
ELSE
BEGIN
Update Pos_WorkStationStock set Stock += ((" + transactionType.ShowRoomFormula + @")*" + txtQty.Text + @") where WorkStationID=" + ddlWorkStationSearch.SelectedValue + @" and ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
END;";
}
}
if (isTransferToanotherShowroom)
{
Pos_TransactionType transactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(transactionTypeID);
if (transactionType.ShowRoomFormula != "0")
{
sql += @"/*
set @Count=
(
select COUNT(*) from Pos_WorkStationStock
where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @" and WorkStationID=" + ddlWorkStationSearch.SelectedValue + @"
)
if @Count = 0
BEGIN
INSERT INTO [Pos_WorkStationStock]
([WorkStationID]
,[ProductID]
,[Stock])
VALUES(" + ddlWorkStationSearch.SelectedValue + @"," + pos_Transaction.Pos_ProductID.ToString() + @"," + txtQty.Text + @");
END
ELSE
BEGIN
Update Pos_WorkStationStock set Stock += ((+1)*" + txtQty.Text + @") where WorkStationID=" + ddlWorkStationSearch.SelectedValue + @" and ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
END;
*/
Update Pos_WorkStationStock set Stock += ((-1)*" + txtQty.Text + @") where WorkStationID=" + ddlWorkStationSearch.SelectedValue + @" and ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
";
}
}
if (isAdjustment)
{
Pos_TransactionType transactionType = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(transactionTypeID);
if (transactionType.ShowRoomFormula != "0")
{
sql += @"
set @Count=
(
select COUNT(*) from Pos_WorkStationStock
where ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @" and WorkStationID=" + ddlWorkStationSearch.SelectedValue + @"
)
if @Count = 0
BEGIN
INSERT INTO [Pos_WorkStationStock]
([WorkStationID]
,[ProductID]
,[Stock])
VALUES(" + ddlWorkStationSearch.SelectedValue + @"," + pos_Transaction.Pos_ProductID.ToString() + @"," + txtQty.Text + @");
END
ELSE
BEGIN
Update Pos_WorkStationStock set Stock += ((" + (transactionTypeID == 23?"":"-") + @"1)*" + txtQty.Text + @") where WorkStationID=" + ddlWorkStationSearch.SelectedValue + @" and ProductID=" + pos_Transaction.Pos_ProductID.ToString() + @";
END;
";
}
}
}
CommonManager.SQLExec(sql);
btnSearch_Click(this, new EventArgs());
hlnkProductionPrint.Visible = true;
hlnkProductionPrint.NavigateUrl = "TransactionPrint.aspx?Pos_TransactionMasterID=" + TransactionMasterID.ToString();
doSearch(true);
}
private void applyRole()
{
List <Button> allAllowedButton = ButtonManager.GetAllButtonsByPageURLnUserID(HttpContext.Current.Request.Url.AbsoluteUri, getLogin().LoginID);
//load the transaction Type rbtn
List <Pos_TransactionType> allTransactionType = Pos_TransactionTypeManager.GetAllPos_TransactionTypes();
foreach (Pos_TransactionType type in allTransactionType)
{
foreach (Button btn in allAllowedButton)
{
if (btn.ButtonName == ("rbtn" + type.TransactionTypeName.Replace(" ", "")))
{
rbtnTransactionType.Items.Add(new ListItem(type.TransactionTypeName, type.Pos_TransactionTypeID.ToString()));
break;
}
}
}
rbtnTransactionType.SelectedValue = "13";
trSupplier.Visible = false;
trWorkStation.Visible = false;
ddlWorkStationForTransactionReport.Enabled = false;
btnGenerateLinkForTransacationByID.Visible = false;
btnDelivaryChalan.Visible = false;
btnDeleteTransaction.Visible = false;
btnCentralProductStockReport.Visible = false;
btnWorkStationStockReport.Visible = false;
btnLedgerSup.Visible = false;
btnLedger.Visible = false;
btnLedgerBR.Visible = false;
btnCentralStockReport.Visible = false;
btnShowroomStockReport.Visible = false;
btnShowroomSalesReport.Visible = false;
btnSalesPersonWiseSalesReport.Visible = false;
btnDateWiseSalesReport.Visible = false;
btnDayWiseSalesSummary.Visible = false;
foreach (Button btn in allAllowedButton)
{
if (btn.ButtonName == "trSupplier")
{
trSupplier.Visible = true;
}
if (btn.ButtonName == "trWorkStation")
{
trWorkStation.Visible = true;
}
if (btn.ButtonName == "trWorkStation_Enabled")
{
ddlWorkStationForTransactionReport.Enabled = true;
}
else
{
ddlWorkStationForTransactionReport.SelectedValue = getLogin().ExtraField5;
}
if (btn.ButtonName == "btnGenerateLinkForTransacationByID")
{
btnGenerateLinkForTransacationByID.Visible = true;
}
if (btn.ButtonName == "btnDelivaryChalan")
{
btnDelivaryChalan.Visible = true;
}
if (btn.ButtonName == "btnDeleteTransaction")
{
btnDeleteTransaction.Visible = true;
}
if (btn.ButtonName == "btnCentralProductStockReport")
{
btnCentralProductStockReport.Visible = true;
}
if (btn.ButtonName == "btnWorkStationStockReport")
{
btnWorkStationStockReport.Visible = true;
}
if (btn.ButtonName == "btnLedgerSup")
{
//btnLedgerSup.Visible = true;
}
if (btn.ButtonName == "btnLedger")
{
btnLedger.Visible = true;
}
if (btn.ButtonName == "btnLedgerBR")
{
//btnLedgerBR.Visible = true;
}
if (btn.ButtonName == "btnCentralStockReport")
{
btnCentralStockReport.Visible = true;
}
if (btn.ButtonName == "btnShowroomStockReport")
{
btnShowroomStockReport.Visible = true;
}
if (btn.ButtonName == "btnShowroomSalesReport")
{
btnShowroomSalesReport.Visible = true;
}
if (btn.ButtonName == "btnSalesPersonWiseSalesReport")
{
btnSalesPersonWiseSalesReport.Visible = true;
}
if (btn.ButtonName == "btnDateWiseSalesReport")
{
btnDateWiseSalesReport.Visible = true;
}
if (btn.ButtonName == "btnDayWiseSalesSummary")
{
btnDayWiseSalesSummary.Visible = true;
}
}
}
private void loadData()
{
int WorkStationID = 0;
try
{
WorkStationID = Int32.Parse(Request.QueryString["WorkStationID"]);
}
catch (Exception ex)
{
WorkStationID = 0;
}
int TransactionTypeID = 0;
try
{
TransactionTypeID = Int32.Parse(Request.QueryString["TransactionTypeID"]);
}
catch (Exception ex)
{
TransactionTypeID = 0;
}
string fromDate = (Request.QueryString["FromDate"] != null ? Request.QueryString["FromDate"].ToString() : "");
string toDate = (Request.QueryString["ToDate"] != null ? Request.QueryString["ToDate"].ToString() : "");
lblStockDate.Text = DateTime.Parse(fromDate).ToString("dd/MM/yyyy") + " to " + DateTime.Parse(toDate).ToString("dd/MM/yyyy");
//purchase info
string sql = @"SELECT [Pos_TransactionMasterID]
,[TransactionDate]
,[Pos_TransactionType].TransactionTypeName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text as FromOrTo
,ByWorkStation.ChartOfAccountLabel4Text as ByWorkStatonName
,[TransactionID]
,Pos_TransactionType.Pos_TransactionTypeID
FROM [Pos_TransactionMaster]
inner join Pos_TransactionType on Pos_TransactionMaster.Pos_TransactionTypeID
=Pos_TransactionType.Pos_TransactionTypeID
inner join ACC_ChartOfAccountLabel4 on ACC_ChartOfAccountLabel4.ACC_ChartOfAccountLabel4ID
=Pos_TransactionMaster.ToOrFromID
inner join ACC_ChartOfAccountLabel4 as ByWorkStation on ByWorkStation.ACC_ChartOfAccountLabel4ID
=Pos_TransactionMaster.WorkSatationID
where Pos_TransactionMaster.Pos_TransactionTypeID = " + TransactionTypeID.ToString() + @" and (Pos_TransactionMaster.TransactionDate between '" + fromDate + "' and '" + toDate + "')";
if (TransactionTypeID != 0)
{
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(TransactionTypeID).TransactionTypeName;
}
if (WorkStationID != 0)
{
if (
TransactionTypeID == 9
//||
//TransactionTypeID==12
)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + WorkStationID;
}
else
{
sql += " and Pos_TransactionMaster.WorkSatationID =" + WorkStationID;
}
}
sql += @"
order by TransactionDate,[Pos_TransactionType].TransactionTypeName,
ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
";
DataSet ds = CommonManager.SQLExec(sql);
gvTransactionIDs.DataSource = ds.Tables[0];
gvTransactionIDs.DataBind();
}
private void loadData()
{
int suppliyerID = 0;
try
{
suppliyerID = Int32.Parse(Request.QueryString["SuppliyerID"]);
}
catch (Exception ex)
{
suppliyerID = 0;
}
int productID = 0;
try
{
productID = Int32.Parse(Request.QueryString["ProductID"]);
}
catch (Exception ex)
{
productID = 0;
}
int WorkStationID = 0;
try
{
WorkStationID = Int32.Parse(Request.QueryString["WorkStationID"]);
}
catch (Exception ex)
{
WorkStationID = 0;
}
int TransactionTypeID = 0;
try
{
TransactionTypeID = Int32.Parse(Request.QueryString["TransactionTypeID"]);
}
catch (Exception ex)
{
TransactionTypeID = 0;
}
string fromDate = (Request.QueryString["FromDate"] != null ? Request.QueryString["FromDate"].ToString() : "");
string toDate = (Request.QueryString["ToDate"] != null ? Request.QueryString["ToDate"].ToString() : "");
lblStockDate.Text = DateTime.Parse(fromDate).ToString("dd/MM/yyyy") + " to " + DateTime.Parse(toDate).ToString("dd/MM/yyyy");
//purchase info
string sql = @"select SUM(Pos_Transaction.Quantity) as Quantity
,Inv_QuantityUnit.QuantityUnitName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
,ACC_ChartOfAccountLabel4.ExtraField1
,SUM(Pos_Transaction.Quantity * Pos_Product.SalePrice) as TotalAmount
,(SUM(Pos_Transaction.Quantity * Pos_Product.SalePrice)/SUM(Pos_Transaction.Quantity)) as UnitPrice
,Pos_Product.BarCode,Pos_Product.ProductID,Pos_Size.SizeName,Pos_Product.Pic2
from
Pos_Product
inner join Pos_Size on Pos_Size.Pos_SizeID= Pos_Product.Pos_SizeID
inner join ACC_ChartOfAccountLabel4 on ACC_ChartOfAccountLabel4.ACC_ChartOfAccountLabel4ID= Pos_Product.ProductID
inner join Inv_QuantityUnit on Inv_QuantityUnit.Inv_QuantityUnitID=Pos_Product.Inv_QuantityUnitID
inner join Pos_Transaction on Pos_Transaction.Pos_ProductID =Pos_Product.Pos_ProductID
inner join Pos_TransactionMaster on Pos_TransactionMaster.Pos_TransactionMasterID = Pos_Transaction.Pos_ProductTransactionMasterID
where Pos_TransactionMaster.Pos_TransactionTypeID =" + TransactionTypeID + @" and (Pos_TransactionMaster.TransactionDate between '" + fromDate + "' and '" + toDate + "')";
if (suppliyerID != 0)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + suppliyerID;
}
if (productID != 0)
{
sql += " and Pos_Product.ProductID =" + productID;
}
if (WorkStationID != 0)
{
if (
TransactionTypeID == 9
//||
//TransactionTypeID == 12
)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + WorkStationID;
}
else
{
sql += " and Pos_TransactionMaster.WorkSatationID =" + WorkStationID;
}
}
sql += @"
group by Inv_QuantityUnit.QuantityUnitName
,ACC_ChartOfAccountLabel4.ChartOfAccountLabel4Text
,ACC_ChartOfAccountLabel4.ExtraField1
,Pos_Product.BarCode,Pos_Product.ProductID,Pos_Size.SizeName,Pos_Product.Pic2
order by Pos_Product.ProductID,Pos_Product.BarCode;";
sql += @"
select distinct 0 as colNo, Pos_Product.Pos_SizeID,Pos_Size.SizeName, 0 as Counting, 0 as GrandTotal,0 as Subtotal
from
Pos_Product
inner join Pos_Size on Pos_Size.Pos_SizeID= Pos_Product.Pos_SizeID
inner join ACC_ChartOfAccountLabel4 on ACC_ChartOfAccountLabel4.ACC_ChartOfAccountLabel4ID= Pos_Product.ProductID
inner join Inv_QuantityUnit on Inv_QuantityUnit.Inv_QuantityUnitID=Pos_Product.Inv_QuantityUnitID
inner join Pos_Transaction on Pos_Transaction.Pos_ProductID =Pos_Product.Pos_ProductID
inner join Pos_TransactionMaster on Pos_TransactionMaster.Pos_TransactionMasterID = Pos_Transaction.Pos_ProductTransactionMasterID
where Pos_TransactionMaster.Pos_TransactionTypeID =" + TransactionTypeID + @" and (Pos_TransactionMaster.TransactionDate between '" + fromDate + "' and '" + toDate + "')";
if (suppliyerID != 0)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + suppliyerID;
}
if (productID != 0)
{
sql += " and Pos_Product.ProductID =" + productID;
}
if (WorkStationID != 0)
{
if (
TransactionTypeID == 9
//||
//TransactionTypeID == 12
)
{
sql += " and Pos_TransactionMaster.ToOrFromID =" + WorkStationID;
}
else
{
sql += " and Pos_TransactionMaster.WorkSatationID =" + WorkStationID;
}
}
sql += @"
order by Pos_Product.Pos_SizeID;
";
if (suppliyerID != 0)
{
sql += "select ChartOfAccountLabel4Text,ACC_ChartOfAccountLabel4ID,ExtraField2 from ACC_ChartOfAccountLabel4 where ACC_ChartOfAccountLabel4ID =" + suppliyerID;
}
else
if (WorkStationID != 0)
{
sql += "select ChartOfAccountLabel4Text,ACC_ChartOfAccountLabel4ID,ExtraField2 from ACC_ChartOfAccountLabel4 where ACC_ChartOfAccountLabel4ID =" + WorkStationID;
}
lblVoucherType.Text = Pos_TransactionTypeManager.GetPos_TransactionTypeByID(TransactionTypeID).TransactionTypeName;
DataSet ds = CommonManager.SQLExec(sql);
int i = 0;
string SizeHeader = "<table cellspacing='0'><tr>";
foreach (DataRow dr in ds.Tables[1].Rows)
{
dr["colNo"] = ++i;
SizeHeader += "<td style='border:1px solid black;min-width:26px;'>" + dr["SizeName"].ToString() + "</td>";
}
SizeHeader += "</tr></table>";
string ProductID = "0";
decimal Total = 0;
decimal Subtotal = 0;
decimal TotalAmount = 0;
decimal SubtotalAmount = 0;
int serialNo = 1;
string htmlTable = @" <table id='itemList_tbl' style='border:1px solid black;width:100%;' cellpadding='0' cellspacing='0'>
";
if (suppliyerID != 0)
{
htmlTable += @"<tr>
<td colspan='2' style='padding-left:50px;border-top:1px solid black;'>
<b>Supplier name:</b> " + ds.Tables[2].Rows[0]["ChartOfAccountLabel4Text"].ToString() + @"
</td>
<td colspan='2' style=' border-top:1px solid black;'>
<b>Supplier ID:</b> " + ds.Tables[2].Rows[0]["ACC_ChartOfAccountLabel4ID"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='4' style='padding-left:50px;'>
<b>Supplier Address:</b> " + ds.Tables[2].Rows[0]["ExtraField2"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='4' style='padding-left:50px; border-bottom:1px solid black;'>
<b>Transaction Date:</b> " + DateTime.Parse(fromDate).ToString("dd MMM yyyy") + " To " + DateTime.Parse(toDate).ToString("dd MMM yyyy") + @"
</td>
</tr>";
}
if (WorkStationID != 0)
{
htmlTable += @"<tr>
<td colspan='2' style='padding-left:50px;border-top:1px solid black;'>
<b>WorkStation name:</b> " + ds.Tables[2].Rows[0]["ChartOfAccountLabel4Text"].ToString() + @"
</td>
<td colspan='2' style=' border-top:1px solid black;'>
<b>ID:</b> " + ds.Tables[2].Rows[0]["ACC_ChartOfAccountLabel4ID"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='4' style='padding-left:50px;'>
<b>Address:</b> " + ds.Tables[2].Rows[0]["ExtraField2"].ToString() + @"
</td>
</tr>
<tr>
<td colspan='4' style='padding-left:50px; border-bottom:1px solid black;'>
<b>Transaction Date:</b> " + DateTime.Parse(fromDate).ToString("dd MMM yyyy") + " To " + DateTime.Parse(toDate).ToString("dd MMM yyyy") + @"
</td>
</tr>";
}
else
{
htmlTable += @"<tr>
<td colspan='4' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;'>
All Workstation
</td>
</tr>";
}
string lastBarCode = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (lastBarCode != dr["Pic2"].ToString())
{
if (lastBarCode != "")
{
foreach (DataRow drsize in ds.Tables[1].Rows)
{
htmlTable += "<td style='border:1px solid black;min-width:26px;'>";
if (int.Parse(drsize["Counting"].ToString()) != 0)
{
htmlTable += drsize["Counting"].ToString();
}
else
{
htmlTable += " ";
}
htmlTable += "</td>";
drsize["Counting"] = 0;
}
htmlTable += @"</tr></table></td>
</tr>";
}
}
if (ProductID != "0" && ProductID != dr["ProductID"].ToString())
{
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td><table><tr>";
foreach (DataRow drsize in ds.Tables[1].Rows)
{
htmlTable += "<td style='border:1px solid black;min-width:26px;'>";
if (int.Parse(drsize["Subtotal"].ToString()) != 0)
{
htmlTable += drsize["Subtotal"].ToString();
}
else
{
htmlTable += " ";
}
htmlTable += "</td>";
drsize["Subtotal"] = 0;
}
htmlTable += @"</tr></table></td>
</tr>";
Subtotal = 0;
SubtotalAmount = 0;
}
if (ProductID != dr["ProductID"].ToString())
{
ProductID = dr["ProductID"].ToString();
htmlTable += @"<tr>
<td colspan='4' style='padding-left:50px; border-top:1px solid black;border-bottom:1px solid black;'>
<b>Item:</b> " + dr["ChartOfAccountLabel4Text"].ToString() + @"
</td>
</tr>
<tr id='tableHeader'>
<td style='border-left:0px;'>S/L</td>
<td>Item Code</td>
<td>Item Name</td>
<td>" + SizeHeader + @"
</td>
</tr>";
}
if (lastBarCode != dr["Pic2"].ToString())
{
htmlTable += @"<tr class='itemCss'>
<td style='border-left:0px;'>" + (serialNo++).ToString() + @"</td>
<td>" + dr["Pic2"].ToString() + @"</td>
<td>" + dr["ChartOfAccountLabel4Text"].ToString() + @"</td>
<td style='padding-left:50px;padding:5px;'><table cellspacing='0'><tr>";
lastBarCode = dr["Pic2"].ToString();
}
if (lastBarCode == dr["Pic2"].ToString())
{
foreach (DataRow drsize in ds.Tables[1].Rows)
{
if (dr["SizeName"].ToString() == drsize["SizeName"].ToString())
{
drsize["Counting"] = int.Parse(dr["Quantity"].ToString().Split('.')[0]);
drsize["GrandTotal"] = int.Parse(drsize["GrandTotal"].ToString()) + int.Parse(dr["Quantity"].ToString().Split('.')[0]);
drsize["Subtotal"] = int.Parse(drsize["Subtotal"].ToString()) + int.Parse(dr["Quantity"].ToString().Split('.')[0]);
}
}
}
/*
* htmlTable += @"<tr class='itemCss'>
* <td style='border-left:0px;'>" + (serialNo++).ToString() + @"</td>
* <td>" + dr["BarCode"].ToString() + @"</td>
* <td>" + dr["ChartOfAccountLabel4Text"].ToString() + @"</td>
* <td style='text-align:right;'>" + decimal.Parse(dr["Quantity"].ToString()).ToString("0,0.00") + @"</td>
* </tr>";
*/
Subtotal += decimal.Parse(dr["Quantity"].ToString());
SubtotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
Total += decimal.Parse(dr["Quantity"].ToString());
TotalAmount += decimal.Parse(dr["TotalAmount"].ToString());
}
//last item
foreach (DataRow drsize in ds.Tables[1].Rows)
{
htmlTable += "<td style='border:1px solid black;min-width:26px;'>";
if (int.Parse(drsize["Counting"].ToString()) != 0)
{
htmlTable += drsize["Counting"].ToString();
}
else
{
htmlTable += " ";
}
htmlTable += "</td>";
drsize["Counting"] = 0;
}
htmlTable += @"</tr></table></td>
</tr>";
//last item subtotal
htmlTable += @"<tr class='subtotalRow'>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td><table><tr>";
foreach (DataRow drsize in ds.Tables[1].Rows)
{
htmlTable += "<td style='border:1px solid black;min-width:26px;'>";
if (int.Parse(drsize["Subtotal"].ToString()) != 0)
{
htmlTable += drsize["Subtotal"].ToString();
}
else
{
htmlTable += " ";
}
htmlTable += "</td>";
drsize["Subtotal"] = 0;
}
htmlTable += @"</tr></table></td>
</tr>";
//grand total
htmlTable += @"<tr id='lastRow'>
<td> </td>
<td> </td>
<td>Sub Total</td>
<td><table><tr>";
foreach (DataRow drsize in ds.Tables[1].Rows)
{
htmlTable += "<td style='border:1px solid black;min-width:26px;'>";
if (int.Parse(drsize["GrandTotal"].ToString()) != 0)
{
htmlTable += drsize["GrandTotal"].ToString();
}
else
{
htmlTable += " ";
}
htmlTable += "</td>";
drsize["GrandTotal"] = 0;
}
htmlTable += @"</tr></table></td>
</tr></table>";
lblItemList.Text += htmlTable;
}